CODEWITHSUNDEEP
sqldatabaseoracle-database
SCJP
SCJP

Reputation: 17

I don't know how to combine my attributes into a single column for this query

My query is to Produce a list of Employees who work for the cinema with the cinema number 07 and include the cinema name in the result. Arrange the result in ascending order by employee surname. Name attributes should be combined into a single output column, and given a sensible header. I've answered most of the query but I'm stuck on the last part . Can somebody help me on this . 

  SELECT Employee.Cinema_No, Cinema.Cinema_Name, Employee.First_Name, 
  Employee.Surname
  FROM Employee
  INNER JOIN Cinema ON Employee.Cinema_No = Cinema.Cinema_No
  WHERE Employee.Cinema_No = 7
  ORDER BY Surname ASC ;

Upvotes: 0

Views: 67

Answers (3)

Leo
Leo

Reputation: 14850

Update

I initially answered this question without realizing it was targetting Oracle. So, if you happened to need a similar query for MS SQL Server you can find the original answer below...

I think your query is fine. You only need to combine the employee's name like below...

  SELECT Employee.Cinema_No, 
      Cinema.Cinema_Name, 
      Employee.First_Name + " " + Employee.Surname as FullName
  FROM Employee
  INNER JOIN Cinema ON Employee.Cinema_No = Cinema.Cinema_No
  WHERE Employee.Cinema_No = 7
  ORDER BY Surname ASC ;

Upvotes: 0

dwir182
dwir182

Reputation: 1549

Concat syntax can help you..

Definition:

CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

SELECT 
  Employee.Cinema_No, 
  Cinema.Cinema_Name, 
  Concat(Employee.First_Name, ' ', Employee.Surname) as Named
FROM 
  Employee
  INNER JOIN Cinema ON Employee.Cinema_No = Cinema.Cinema_No
WHERE 
  Employee.Cinema_No = 7
ORDER BY 
  Surname ASC ;

Upvotes: 0

GMB
GMB

Reputation: 222622

All that is left to do is to concatenate the First and Last name of the employee into a single column. For this, Oracle provides the || operator (string concatenation). You can then use an alias to choose a sensible column name, using keyword AS.

Query :

SELECT 
    Employee.Cinema_No, 
    Cinema.Cinema_Name, 
    Employee.First_Name || ' ' ||  Employee.Surname AS Employee_Full_Name
FROM Employee
INNER JOIN Cinema ON Employee.Cinema_No = Cinema.Cinema_No
WHERE Employee.Cinema_No = 7
ORDER BY Surname ASC ;

Upvotes: 3

Related Questions