SQL Practice Questions

SQL Practice Questions

Consider the following schema definitions:

Branch   (branchNo, street, city, postcode)

Staff                       (staffNo, fName,lName, position, sex, DOB, salary, branchNo)

PropertyforRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo,

   staffNo, branchNo)

                Client                     (clientNo, fName, lName, telNo, prefType, maxRent)

                PrivateOwner        (ownerNo, fName, lName, address, telNo)

                Viewing (clientNo, propertyNo, viewDate, comment)

                Registration          (clientNo, branchNo, staffNo, dateJoined)

An instance of the above schemas is given in the last page of the examination. (You may detach and use it if necessary)

               

List the address of all branch offices in London or Bristol.

SELECT *

FROM    branch

WHERE city=’London’ OR city=’bristol’ ;

List the staff with a salary between $10000 and $30000.

SELECT staff_No

FROM Staff

WHERE  salary between 10000 AND 30000;

3. List the staff in descending order of salary.

SELECT staff_No, salary

FROM Staff

ORDER BY salary DESC ;

4. Find the number of different properties viewed in April 2004.

SELECT count (distinct propert_no)

FROM Viewing

WHERE viewDate BETWEEN ‘1-Apr-04’ AND ’30-Apr-04’;

5. Find the minimum, maximum and average staff salary.

SELECT min(salary)  , max(salary),avg(salary)

FROM Staff;

6. For each branch office with more than one member of staff, find the number of staff working in each branch and the sum of their salaries.

SELECT branchNo, count(staffno), sum(salary) 

FROM Staff

GROUP BY branchNo

HAVING count(staffNo) >1;

7. For each branch, list the numbers and names of staff who manage properties, including the city in which the branch is located and the properties that the staff manage.

SELECT b.branchNo, b.city, s.staffNo, fName, lName, properyNo

FROM Branch AS b, Staff AS s, propertyforRent p

WHERE b.branchNo = s.branchNo AND s.staffNo=p.staffno;

9.Give all managers 5% increase to their salary

UPDATE staff

SET salary=salary*1.05

WHERE position=’Manager’

10.Delete all viewings that belong to property with property number PG4.

DELETE FROM viewing 

WHERE propertyNo=’P64’

Q) Given the following schema definitions:

message (message_id, subject, body)

sent_to(message_id, email, senddate)

customer (email, name, family_size, address)

Q) Find the names and emails of all customers who have sent the message with subject “Happy New Year”  

select customer.email, name 

from message, sent_to, customer

where subject=’Happy New Year’ AND

message.message_id=sent_to.message_id AND

sent_to.email=customer.email

Q) Consider the following relation schema for an airline database.

customer(id, name, age, gender)

onFlight(id, flightNo, flightDate)

flightInfo(flightNo, fromCity, toCity, startTime, duration)

1. Names of all customers above the age of 10

SELECT  name

FROM customer

WHERE  age>10;

2. Names of passengers who flew on flight “TK102” .

SELECT name

FROM customer, onFlight

WHERE   customer.id=onFlight.id AND   

onFlight.flightNo=”TK102”;

3. The number of passengers on flight “TK101” on “1/2/1999”

SELECT    count(id )

FROM onFlight

WHERE flightNo= “TK101” AND flightDate=“1/2/1999”;

Q) Consider the following table:

NAME	OWNER	SPECIES	SEX	BIRTH	DEATH
Claws	Gwen	Cat	M	1994-03-17	NULL
Fang	Benny	Dog	F	1990-08-27	NULL
Browser	Gwen	Bird	F	1994-02-18	1996-05-12
Slim	Diane	Dog	M	1992-08-27	NULL

TABLE NAME:PET

PREDICT OUTPUT:

1)       SELECT CONCAT(CONCAT(SPECIES,’-‘,SEX),NAME) FROM PET;

Cat-MCLAWS Dog-FFANG Bird-FBROWSER Dog-MSLIM

2)       SELECT SUBSTR(NAME,1,3),SPECIES FROM PET WHERE SEX=’F’;

Dog

Bir

3)       SELECT CONCAT(SEX,NULL) FROM PET;

NULL

Questıon) Dıfferentıate between drop table and delete table command ın sql.

A)      Drop table deletes the entire table’s content along with its structure whereas with delete command we can only delete the content”s of the table while preserving the structure.

Q) Exame the structure of the employee table.

Create table employee ( eid number primary key,

Firstname varhcar(20),

Lastname varchar(25));

Which three statements insert rows into the table.

A)      İnsert into employees values(NULL,’JOHN’,’SMITH’);

B)      İnsert into employees values(’JOHN’,’SMITH’);

C)      İnsert into employees values(’123’,’SEBNY’,NULL);

D)      İnsert into employees eid values(123);

E)      İnsert into employees values(’123’,’SEBNY’,’SEN’);

Answer) only c,d and e..... a and b are incorrect.

Q)What is wrong with the following statement.

Delete eid,salary from employee where deptid=90;

Answer) We can not specify column names with the delete command.

Q)Consider the following schema

Empl(empno,ename,job,mgr,hiredate,sal,comm,depto)

Answer the following questions:

1)       Display the jobs where the number of employees is less than 3.

Select job,count(*)

From empl

Group by job

Having count(*)<3;

2)       Display average sal of each department for the jobs ‘Clerk’ and ‘Salesman’ only.

Select deptno,job,avg(sal)

From empl

Group by deptno,job

Having job in (‘Clerk’,’Salesman’);

3)Display the difference of highest and lowest salary of each department having maximum  

    salary>4000.

    Select max(sal)-min(sal) “difference sal”

    From empl

    Group by deptno

    Having max(sal)>4000;

Q) Conside the following schema

Customers(name,address,state,zip,phone,remarks)

Orders(orderdate,name,partno,quanity,remarks,salesmancode)

Parts(partnum,description,price)

Answer the following questions:

1)Count the number of salespeople registring orders for each day.

Select orderdate,count(distinct salesmancode)

From orders

Group by orderdate;

2)Calculate orders for each day and place the result in descending order of the orderdate.

                                Select orderdate,sum(o.quantity*p.price)

From orders o,parts p

Where o.partno=p.partnum

Group by orderdate

Order by orderdate desc;

3)       Determine total order amount for part ‘Roadbike’.

Select sum(o.quanity*p.price) ‘Total amount’

From orders o,parts p

Where o.partno=p.partnum

And p.description=’Roadbike’;

Q) Consider the following schema

Book(Bookid,Title,PublisherName)

BookAuthors(Bookid,Authorname)

Publisher(Name,Address,Phone)

BookCopies(Bookid,Branchid,NoOfcopies)

BookLoans(Bookid,Branchid,Cardno,Dateot,Duedate)

LibraryBranch(Branchid,branchname,address)

Borrower(Cardno,name,address,phone)

1)       How many copies of the book titled ‘The lost tribe’ are owned by the library branch whose name is ‘Sharpstown’?

Select  bc.NoOfcopies

From  book b,bookcopies bc,librarybranch lb

Where  b.bookid=bc.bookid and

             Bc.branchid=lb.branchid and

             Title=’The lost tribe’ and branchname=’Sharpstown’;

2)       How many copies of the book titled ‘The lost tribe’ are owned by each library branch.?

Select bc.NoOfcopies ,lb.branchname

From  b.bookid=bc.bookid and

             Bc.branchid=lb.branchid and

                        Title=’The lost tribe’;

3)       For each book that is loaned out from ‘Sharpstown’ branch and whose due date is today, retrieve the book title,borrower;s name and address.

Select b.title,r.name,r.address

From book b,borrower r,bookloans bl,librarybranch lb

Where b.bookid=bl.bookid and

Bl.cardno=r.cardno and

Lb.branchid=bl.branchid and

Bl.duedate=date(curdate());

4)       For each library branch,retrieve the branch name and the total number of books loaned out from each branch.

Select l.branchname,count(*)

From librarybranch l,bookloans bl

Where bl.branchid=l.branchid

Group by l.branchname;

5)       Retrieve the names,address,and the number of books checked out for all the borrowers who have more than five books checked out.

Select b.name,b.address,count(*)

From borrower r,bookloans bl

Where r.cardno=bl.cardno

Group by b.cardno

Having count(*)>5;

6)       For each book authored by ‘Stephen king’, retrieve the title and the number of copies owned by the library branch whose name is ‘Central’.

Select title,noofcopies

From book ,bookauthors,bookcopies,librarybranch

Where book.bookid=bookauthors.bookid and

            Book.bookid=bookcopies.bookid and

             Bookcopies.branchid=librarybranch.branchid and

             Authorname=’Stephen king’ and

             Branchname=’Central’;