Database Management Systems (3130703) MCQs

MCQs of SQL Concepts

Showing 51 to 60 out of 68 Questions
3 4 5 6 7
51.

Write SQL statements (Query) for following tables:

Department(DepartmentID (PK), DepartmentName, DepartmentCode, Location)

Person(PeronID (PK), PersonName, DepartmentID (FK), Salary, JoiningDate, City)

Find all departments whose total salary is exceeding 100000.

(a)

SELECT [dbo].[Department].[DepartmentName], SUM([dbo].[Person].[Salary]) AS Total_Sal

FROM [dbo].[Person]

LEFT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Department].[DepartmentName]

HAVING SUM([dbo].[Person].[Salary]) > 100000
(b)

SELECT [dbo].[Department].[DepartmentName], SUM([dbo].[Person].[Salary]) AS Total_Sal

FROM [dbo].[Person]

RIGHT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Department].[DepartmentName]

HAVING SUM([dbo].[Person].[Salary]) > 100000
(c)

SELECT [dbo].[Department].[DepartmentName], SUM([dbo].[Person].[Salary]) AS Total_Sal

FROM [dbo].[Person]

FULL OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Department].[DepartmentName]

HAVING SUM([dbo].[Person].[Salary]) > 100000
(d)

SELECT [dbo].[Department].[DepartmentName], SUM([dbo].[Person].[Salary]) AS Total_Sal

FROM [dbo].[Person]

INNER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Department].[DepartmentName]

HAVING SUM([dbo].[Person].[Salary]) > 100000
Answer:

Option (a)

52.

Write SQL statements (Query) for following tables:

Department(DepartmentID (PK), DepartmentName, DepartmentCode, Location)

Person(PeronID (PK), PersonName, DepartmentID (FK), Salary, JoiningDate, City)

Retrieve person name, salary & department name who belongs to Jamnagar.

(a)

SELECT [dbo].[Person].[PersonName], [dbo].[Person].[Salary], [dbo].[Department].[DepartmentName]

FROM [dbo].[Person]

RIGHT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

HAVING [dbo].[Person].[City] = 'Jamnagar'
(b)

SELECT [dbo].[Person].[PersonName], [dbo].[Person].[Salary], [dbo].[Department].[DepartmentName]

FROM [dbo].[Person]

RIGHT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

WHERE [dbo].[Person].[City] = 'Jamnagar'
(c)

SELECT [dbo].[Person].[PersonName], [dbo].[Person].[Salary], [dbo].[Department].[DepartmentName]

FROM [dbo].[Person]

LEFT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

HAVING [dbo].[Person].[City] = 'Jamnagar'
(d)

SELECT [dbo].[Person].[PersonName], [dbo].[Person].[Salary], [dbo].[Department].[DepartmentName]

FROM [dbo].[Person]

LEFT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

WHERE [dbo].[Person].[City] = 'Jamnagar'
Answer:

Option (d)

53.

Write SQL statements (Query) for following tables:

Department(DepartmentID (PK), DepartmentName, DepartmentCode, Location)

Person(PeronID (PK), PersonName, DepartmentID (FK), Salary, JoiningDate, City)

Find all persons who does not belongs to any department.

(a)

SELECT [dbo].[Person].[PersonName]

FROM [dbo].[Person]

WHOSE [dbo].[Person].[DepartmentID] = NULL
(b)

SELECT [dbo].[Person].[PersonName]

FROM [dbo].[Person]

WHERE [dbo].[Person].[DepartmentID] IS NULL
(c)

SELECT [dbo].[Person].[PersonName]

FROM [dbo].[Person]

WHERE [dbo].[Person].[DepartmentID] = NULL
(d)

SELECT [dbo].[Person].[PersonName]

FROM [dbo].[Person]

WHOSE [dbo].[Person].[DepartmentID] IS NULL
Answer:

Option (b)

54.

Write SQL statements (Query) for following tables:

Department(DepartmentID (PK), DepartmentName, DepartmentCode, Location)

Person(PeronID (PK), PersonName, DepartmentID (FK), Salary, JoiningDate, City)

Find department wise person counts.

(a)

SELECT [dbo].[Department].[DepartmentName], SUM([dbo].[Person].[PersonID]) AS Persons

FROM [dbo].[Person]

LEFT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Department].[DepartmentName]
(b)

SELECT [dbo].[Department].[DepartmentName], COUNT([dbo].[Person].[PersonID]) AS Persons

FROM [dbo].[Person]

LEFT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Department].[DepartmentName]
(c)

SELECT [dbo].[Department].[DepartmentName], COUNT([dbo].[Person].[PersonID]) AS Persons

FROM [dbo].[Person]

RIGHT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Department].[DepartmentName]
(d)

SELECT [dbo].[Department].[DepartmentName], COUNT([dbo].[Person].[PersonID]) AS Persons

FROM [dbo].[Person]

INNER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Department].[DepartmentName]
Answer:

Option (b)

55.

Write SQL statements (Query) for following tables:

Department(DepartmentID (PK), DepartmentName, DepartmentCode, Location)

Person(PeronID (PK), PersonName, DepartmentID (FK), Salary, JoiningDate, City)

Find average salary of person who belongs to Ahmedabad.

(a)

SELECT AVG([dbo].[Person].[Salary]) AS Avg_Sal

FROM [dbo].[Person]

WHOSE [dbo].[Person].[City] = 'Ahmedabad'
(b)

SELECT AVG([dbo].[Person].[Salary]) AS Avg_Sal

FROM [dbo].[Person]

WHERE [dbo].[Person].[City] = 'Ahmedabad'
(c)

SELECT AVG([dbo].[Person].[Salary]) AS Avg_Sal

FROM [dbo].[Person]

WHERE [dbo].[Person].[City] IS 'Ahmedabad'
(d)

SELECT AVG([dbo].[Person].[Salary]) AS Avg_Sal

FROM [dbo].[Person]

HAVING [dbo].[Person].[City] = 'Ahmedabad'
Answer:

Option (b)

56.

Write SQL statements (Query) for following tables:

Department(DepartmentID (PK), DepartmentName, DepartmentCode, Location)

Person(PeronID (PK), PersonName, DepartmentID (FK), Salary, JoiningDate, City)

Produce Output Like: <PersonName> earns <Salary> from department <DepartmentName> (In Single Column)

(a)

SELECT [dbo].[Person].[PersonName] + ' earns '+ CAST([dbo].[Person].[Salary] AS varchar(50)) + ' from department '+ [dbo].[Department].[DepartmentName]

FROM [dbo].[Person]

LEFT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]
(b)

SELECT [dbo].[Person].[PersonName] + ' earns '+ CAST([dbo].[Person].[Salary] AS varchar(50)) + ' from department '+ [dbo].[Department].[DepartmentName]

FROM [dbo].[Person]

RIGHT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]
(c)

SELECT [dbo].[Person].[PersonName] + ' earns '+ CAST([dbo].[Person].[Salary] AS varchar(50)) + ' from department '+ [dbo].[Department].[DepartmentName]

FROM [dbo].[Person]

FULL OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]
(d)

SELECT [dbo].[Person].[PersonName] + ' earns '+ CAST([dbo].[Person].[Salary] AS varchar(50)) + ' from department '+ [dbo].[Department].[DepartmentName]

FROM [dbo].[Person]

CROSS JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]
Answer:

Option (a)

57.

Write SQL statements (Query) for following tables:

Department(DepartmentID (PK), DepartmentName, DepartmentCode, Location)

Person(PeronID (PK), PersonName, DepartmentID (FK), Salary, JoiningDate, City)

List all departments who have no persons.

(a)

SELECT [dbo].[Department].[DepartmentName], COUNT([dbo].[Person].[PersonID]) AS Persons

FROM [dbo].[Person]

RIGHT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Department].[DepartmentName]

WHOSE COUNT([dbo].[Person].[PersonID]) = 0
(b)

SELECT [dbo].[Department].[DepartmentName], COUNT([dbo].[Person].[PersonID]) AS Persons

FROM [dbo].[Person]

LEFT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Department].[DepartmentName]

WHERE COUNT([dbo].[Person].[PersonID]) = 0
(c)

SELECT [dbo].[Department].[DepartmentName], COUNT([dbo].[Person].[PersonID]) AS Persons

FROM [dbo].[Person]

LEFT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Department].[DepartmentName]

HAVING COUNT([dbo].[Person].[PersonID]) = 0
(d)

SELECT [dbo].[Department].[DepartmentName], COUNT([dbo].[Person].[PersonID]) AS Persons

FROM [dbo].[Person]

RIGHT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Department].[DepartmentName]

HAVING COUNT([dbo].[Person].[PersonID]) = 0
Answer:

Option (c)

58.

Write SQL statements (Query) for following tables:

Department(DepartmentID (PK), DepartmentName, DepartmentCode, Location)

Person(PeronID (PK), PersonName, DepartmentID (FK), Salary, JoiningDate, City)

Find city & department wise total, average & maximum salaries.

(a)

SELECT [dbo].[Person].[City], [dbo].[Department].[DepartmentName], SUM([dbo].[Person].[Salary]) AS Total_Sal, AVG([dbo].[Person].[Salary]) AS Avg_Sal, MAX([dbo].[Person].[Salary]) AS Max_Sal

FROM [dbo].[Person]

CROSS JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Person].[City], [dbo].[Department].[DepartmentName]
(b)

SELECT [dbo].[Person].[City], [dbo].[Department].[DepartmentName], SUM([dbo].[Person].[Salary]) AS Total_Sal, AVG([dbo].[Person].[Salary]) AS Avg_Sal, MAX([dbo].[Person].[Salary]) AS Max_Sal

FROM [dbo].[Person]

RIGHT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Person].[City], [dbo].[Department].[DepartmentName]
(c)

SELECT [dbo].[Person].[City], [dbo].[Department].[DepartmentName], SUM([dbo].[Person].[Salary]) AS Total_Sal, AVG([dbo].[Person].[Salary]) AS Avg_Sal, MAX([dbo].[Person].[Salary]) AS Max_Sal

FROM [dbo].[Person]

FULL OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Person].[City], [dbo].[Department].[DepartmentName]
(d)

SELECT [dbo].[Person].[City], [dbo].[Department].[DepartmentName], SUM([dbo].[Person].[Salary]) AS Total_Sal, AVG([dbo].[Person].[Salary]) AS Avg_Sal, MAX([dbo].[Person].[Salary]) AS Max_Sal

FROM [dbo].[Person]

LEFT OUTER JOIN [dbo].[Department]

ON [dbo].[Department].[DepartmentID] = [dbo].[Person].[DepartmentID]

GROUP BY [dbo].[Person].[City], [dbo].[Department].[DepartmentName]
Answer:

Option (d)

59.

Write SQL statements (Query) for following tables:

Employee(Eid, EName, Salary, Deptid)

Deparment(Did, DName, City)

Company(Cmpid, CmpName)

Customers(Cid, FName, LName, City, Sid)

Salesman(Sid, SName, City, Commission)

Products(Pid, PName, Price, Cmpid)

Orders(Oid, Pid, Cid, Sid, Amount, ODate, Quantity)

Display salesman name, customer name and their cities for the salesmen and customer who belongs to the same city.

(a)

SELECT Salesman.SName, Customers.FName, Customers.City FROM Salesman FULL JOIN Customers ON Salesman.Sid=Customers.Sid AND Salesman.City=Customers.city
(b)

SELECT Salesman.SName, Customers.FName, Customers.City FROM Salesman LEFT JOIN Customers ON Salesman.Sid=Customers.Sid AND Salesman.City=Customers.city
(c)

SELECT Salesman.SName, Customers.FName, Customers.City FROM Salesman INNER JOIN Customers ON Salesman.Sid=Customers.Sid AND Salesman.City=Customers.city
(d)

SELECT Salesman.SName, Customers.FName, Customers.City FROM Salesman RIGHT JOIN Customers ON Salesman.Sid=Customers.Sid AND Salesman.City=Customers.city
Answer:

Option (c)

60.

Employee(Eid, EName, Salary, Deptid)

Deparment(Did, DName, City)

Company(Cmpid, CmpName)

Customers(Cid, FName, LName, City, Sid)

Salesman(Sid, SName, City, Commission)

Products(Pid, PName, Price, Cmpid)

Orders(Oid, Pid, Cid, Sid, Amount, ODate, Quantity)

Write a SQL statement to know which salesman are working for which customer.

(a)

SELECT Salesman.SName, Customers.FName As CustomerName

FROM Salesman

INNER JOIN Customers

ON Salesman.Sid=Customers.Sid
(b)

SELECT Salesman.SName, Customers.FName As CustomerName

FROM Salesman

LEFT JOIN Customers

ON Salesman.Sid=Customers.Sid
(c)

SELECT Salesman.SName, Customers.FName As CustomerName

FROM Salesman

RIGHT JOIN Customers

ON Salesman.Sid=Customers.Sid
(d)

SELECT Salesman.SName, Customers.FName As CustomerName

FROM Salesman

FULL JOIN Customers

ON Salesman.Sid=Customers.Sid
Answer:

Option (a)

Showing 51 to 60 out of 68 Questions
3 4 5 6 7