Database Management Systems (3130703) MCQs

MCQs of SQL Concepts

Showing 61 to 68 out of 68 Questions
4 5 6 7
61.

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 the list of customers who appointed a salesman for their jobs who does not live in the same city where their customer lives, and gets a commission is above 12%.

(a)

SELECT Customers.FName As CustomerName

FROM Customers

LEFT JOIN Salesman

ON Salesman.Sid=Customers.Sid

AND Salesman.City <> Customers.City

AND Salesman.Commission > 12
(b)

SELECT Customers.FName As CustomerName

FROM Customers

INNER JOIN Salesman

ON Salesman.Sid=Customers.Sid

AND Salesman.City <> Customers.City

AND Salesman.Commission > 12
(c)

SELECT Customers.FName As CustomerName

FROM Customers

RIGHT JOIN Salesman

ON Salesman.Sid=Customers.Sid

AND Salesman.City <> Customers.City

AND Salesman.Commission > 12
(d)

SELECT Customers.FName As CustomerName

FROM Customers

FULL JOIN Salesman

ON Salesman.Sid=Customers.Sid

AND Salesman.City <> Customers.City

AND Salesman.Commission > 12
Answer:

Option (b)

62.

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 the details of order i.e. order number, order date, amount of order, which customer gives the order and which salesman works for that customer and how much commission he gets for an order.

(a)

SELECT Orders.Oid,

Orders.ODate,

Orders.Amount,

Customers.Fname,

Salesman.Sname,

Salesman.Commission

FROM Customers

FULL JOIN Orders

ON Customers.Cid=Orders.Cid

FULL JOIN Salesman

ON Orders.Sid=Salesman.Sid;
(b)

SELECT Orders.Oid,

Orders.ODate,

Orders.Amount,

Customers.Fname,

Salesman.Sname,

Salesman.Commission

FROM Customers

RIGHT JOIN Orders

ON Customers.Cid=Orders.Cid

RIGHT JOIN Salesman

ON Orders.Sid=Salesman.Sid;
(c)

SELECT Orders.Oid,

Orders.ODate,

Orders.Amount,

Customers.Fname,

Salesman.Sname,

Salesman.Commission

FROM Customers

INNER JOIN Orders

ON Customers.Cid=Orders.Cid

INNER JOIN Salesman

ON Orders.Sid=Salesman.Sid;
(d)

SELECT Orders.Oid,

Orders.ODate,

Orders.Amount,

Customers.Fname,

Salesman.Sname,

Salesman.Commission

FROM Customers

LEFT JOIN Orders

ON Customers.Cid=Orders.Cid

LEFT JOIN Salesman

ON Orders.Sid=Salesman.Sid;
Answer:

Option (c)

63.

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 make a list in ascending order for the customer who works either through a salesman or by own.

(a)

SELECT Customers.FName, Salesman.Sname

FROM Customers

FULL OUTER JOIN Salesman

ON Customers.Sid=Salesman.Sid

ORDER BY Customers.FName;
(b)

SELECT Customers.FName, Salesman.Sname

FROM Customers

LEFT OUTER JOIN Salesman

ON Customers.Sid=Salesman.Sid

ORDER BY Customers.FName;
(c)

SELECT Customers.FName, Salesman.Sname

FROM Customers

RIGHT OUTER JOIN Salesman

ON Customers.Sid=Salesman.Sid

ORDER BY Customers.FName;
(d)

SELECT Customers.FName, Salesman.Sname

FROM Customers

INNER OUTER JOIN Salesman

ON Customers.Sid=Salesman.Sid

ORDER BY Customers.FName;
Answer:

Option (b)

64.

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 Write a SQL statement to make a report with customer name, city, order number, order date, and order amount in ascending order according to the order date to find that either any of the existing customers have placed no order or placed one or more orders.

(a)

SELECT Customers.FName, Customers.City, Orders.Oid,Orders.ODate, Orders.Amount

FROM Customers

RIGHT OUTER JOIN Orders ON Customers.Cid=Orders.Cid

ORDER BY Orders.ODate
(b)

SELECT Customers.FName, Customers.City, Orders.Oid,Orders.ODate, Orders.Amount

FROM Customers

LEFT OUTER JOIN Orders ON Customers.Cid=Orders.Cid

ORDER BY Orders.ODate
(c)

SELECT Customers.FName, Customers.City, Orders.Oid,Orders.ODate, Orders.Amount

FROM Customers

FULL OUTER JOIN Orders ON Customers.Cid=Orders.Cid

ORDER BY Orders.ODate
(d)

SELECT Customers.FName, Customers.City, Orders.Oid,Orders.ODate, Orders.Amount

FROM Customers

INNER OUTER JOIN Orders ON Customers.Cid=Orders.Cid

ORDER BY Orders.ODate
Answer:

Option (b)

65.

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 a list in ascending order for the salesmen who works either for one or more customer or not yet join under any of the customers.

(a)

SELECT Salesman.SName as Salesman, Count(1) AS CustomerCount

FROM Salesman

RIGHT OUTER JOIN Customers ON Customers.Sid=Salesman.Sid

GROUP BY Salesman.SName

ORDER BY Salesman
(b)

SELECT Salesman.SName as Salesman, Count(1) AS CustomerCount

FROM Salesman

INNER OUTER JOIN Customers ON Customers.Sid=Salesman.Sid

GROUP BY Salesman.SName

ORDER BY Salesman
(c)

SELECT Salesman.SName as Salesman, Count(1) AS CustomerCount

FROM Salesman

LEFT OUTER JOIN Customers ON Customers.Sid=Salesman.Sid

GROUP BY Salesman.SName

ORDER BY Salesman
(d)

SELECT Salesman.SName as Salesman, Count(1) AS CustomerCount

FROM Salesman

FULL OUTER JOIN Customers ON Customers.Sid=Salesman.Sid

GROUP BY Salesman.SName

ORDER BY Salesman
Answer:

Option (c)

66.

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 a list for the salesmen who works either for one or more customer or not yet join under any of the customers who placed either one or more orders or no order to their supplier.

(a)

SELECT Salesman.SName as Salesman, Count(Customers.Cid) AS CustomerCount, Count(Orders.Oid) AS Orders

FROM Salesman

FULL OUTER JOIN Customers

ON Customers.Sid=Salesman.Sid

RIGHT OUTER JOIN Orders

ON Orders.Cid=Customers.Cid

GROUP BY Salesman.SName ORDER BY Salesman
(b)

SELECT Salesman.SName as Salesman, Count(Customers.Cid) AS CustomerCount, Count(Orders.Oid) AS Orders

FROM Salesman

LEFT OUTER JOIN Customers

ON Customers.Sid=Salesman.Sid

FULL OUTER JOIN Orders

ON Orders.Cid=Customers.Cid

GROUP BY Salesman.SName ORDER BY Salesman
(c)

SELECT Salesman.SName as Salesman, Count(Customers.Cid) AS CustomerCount, Count(Orders.Oid) AS Orders

FROM Salesman

LEFT OUTER JOIN Customers

ON Customers.Sid=Salesman.Sid

RIGHT OUTER JOIN Orders

ON Orders.Cid=Customers.Cid

GROUP BY Salesman.SName ORDER BY Salesman
(d)

SELECT Salesman.SName as Salesman, Count(Customers.Cid) AS CustomerCount, Count(Orders.Oid) AS Orders

FROM Salesman

LEFT OUTER JOIN Customers

ON Customers.Sid=Salesman.Sid

LEFT OUTER JOIN Orders

ON Orders.Cid=Customers.Cid

GROUP BY Salesman.SName ORDER BY Salesman
Answer:

Option (d)

67.

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 make a Cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for that customer who belongs to a city.

(a)

SELECT * FROM Customers

LEFT JOIN Salesman

WHERE Customers.City IS NOT NULL
(b)

SELECT * FROM Customers

CROSS JOIN Salesman

WHERE Customers.City IS NOT NULL
(c)

SELECT * FROM Customers

RIGHT JOIN Salesman

WHERE Customers.City IS NOT NULL
(d)

SELECT * FROM Customers

FULL JOIN Salesman

WHERE Customers.City IS NOT NULL
Answer:

Option (b)

68.

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 query to display the names of the company whose products have an average price larger than or equal to Rs. 350.

(a)

SELECT Company.CmpName, AVG(Products.Price) As AveragePrice

FROM Company

CROSS JOIN Products

ON Company.Cmpid=Products.Cmpid

GROUP BY Company.CmpName

HAVING AVG(Products.Price) >= 350;
(b)

SELECT Company.CmpName, AVG(Products.Price) As AveragePrice

FROM Company

RIGHT JOIN Products

ON Company.Cmpid=Products.Cmpid

GROUP BY Company.CmpName

HAVING AVG(Products.Price) >= 350;
(c)

SELECT Company.CmpName, AVG(Products.Price) As AveragePrice

FROM Company

LEFT JOIN Products

ON Company.Cmpid=Products.Cmpid

GROUP BY Company.CmpName

HAVING AVG(Products.Price) >= 350;
(d)

SELECT Company.CmpName, AVG(Products.Price) As AveragePrice

FROM Company

INNER JOIN Products

ON Company.Cmpid=Products.Cmpid

GROUP BY Company.CmpName

HAVING AVG(Products.Price) >= 350;
Answer:

Option (d)

Showing 61 to 68 out of 68 Questions
4 5 6 7