SQL 1-60

1. Define a degree of a relation

• How many rows a table has

• how long each tuple is, or how many columns the table has

• how many different tuples there are

• how many different datatypes table has

60. Which of the following anomaly does not exist?

• Creation anomaly

• Deletion anomaly

• Insertion anomaly

• Modification anomaly

59. How many types of anomalies exist?

• 3

• 2

• 1

• 4

58. Retrieve all students’ name and scholarship, tutors name except students who have scholarship 3000$. Tables: OFFICE {id (PK), locations, name}, TUTOR {id (PK), name, officeid (FK references office (id)), experience}, STUDENTS {id (PK), name, scholarship, registereddate, tutorid (FK references tutor (id))

• select s.name, t.name from students s join tutor t on s.tutorid=t.id where s.scholarship <> 3000;

• select s.name, t.name from students s join tutor t on s.tutorid=t.id where s.scholarship = 3000;

• select s.name, t.name from students s join tutor t on s.tutorid=t.id where s.scholarship < 3000;

• select s.name, t.name from students s join tutor t on s.tutorid=t.id where s.scholarship is 3000;

57. Retrieve office name and the number of students for each of them. Tables: OFFICE {id (PK), locations, name}, TUTOR {id (PK), name, officeid (FK references office (id)), experience}, STUDENTS {id (PK), name, scholarship, registereddate, tutorid (FK references tutor (id))}.

• select count (s.name) as numberofstudents, o.name from tutor t join students s on t.id=s.tutoridjoin office o on o.id=t.officeid group by o.name;

• select count (s.name) as numberofstudents, o.name from students s on join office o on o.id=s.id group by o.name;

• select count (s.name) as numberofstudents, o.name from tutor t join students s on t.id=s.tutoridjoin office o o.id=t.officeid group by o.name;

• select count (s.name) as numberofstudents, o.name from tutor t join students s on t.id=s.idjoin office o on o.id=t.id group by o.name;

56. Retrieve tutors’ name and the number of students for each of them. Tables: OFFICE {id (PK), locations, name}, TUTOR {id (PK), name, officeid (FK references office (id)), experience}, STUDENTS {id (PK), name, scholarship, registereddate, tutorid (FK references tutor (id))}.

• select count (s.name) as numberofstudents, t.name from tutor t join students s on t.id=s.tutoridgroup by t.name;

• select count (s.name) as numberofstudents, t.name from tutor t join students s on t.id=s.tutorid;

• select count (s.name) as numberofstudents, t.name from tutor t join students s on t.id=s.idgroup by t.name;

• select count (s.name) as numberofstudents, t.name from tutor t join students s on t.id=s.id;

55. Retrieve all tutors and students names. Tables: OFFICE {id (PK), locations, name}, TUTOR {id (PK), name, officeid (FK references office (id)), experience}, STUDENTS {id (PK), name, scholarship, registereddate, tutorid (FK references tutor (id))}.

• select t.name, s.name from tutor t full join students s on t.id=s.tutorid;

• select t.name, s.name from tutor t full join students s t.id=s.tutorid;

• select t.name, s.name from tutor t full join students s t.id=s.id;

• select t.name, s.name from tutor t full join students s on t.id=s.id;

54. Retrieve office name and tutor name, which have maximum experienced tutors. Tables: OFFICE {id (PK), locations, name}, TUTOR {id (PK), name, officeid (FK references office (id)), experience}, STUDENTS {id (PK), name, scholarship, registereddate, tutorid (FK references tutor (id))}.

• select o.name, t.name from office o join tutor t on o.id=t.officeid where t.experience = (select max (experience) from tutor);

• select o.name, t.name from office o join tutor t on o.id=t.officeid where t.experience = (select min (experience) from tutor);

• select o.name, t.name from office o join tutor t on o.id=t.officeid where t.experience = (select high (experience) from tutor);

• select o.name, t.name from office o join tutor t on o.id=t.officeid where t.experience = (select low (experience) from tutor);

53. Retrieve information about office and their tutors as well. Tables: OFFICE {id (PK), locations, name}, TUTOR {id (PK), name, officeid (FK references office (id)), experience}, STUDENTS {id (PK), name, scholarship, registereddate, tutorid (FK references tutor (id))}.d

• select *from office o join tutor t on o.id=t.officeid;

• select *from office o join tutor t in o.id=t.officeid;

• select *form office o join tutor t on o.id=t.officeid;

• select *from office o join tutor t on o.id=t.id;

52. Retrieve students name and scholarship along with their department name, who have scholarship between 4000 and 5000. Tables: OFFICE {id (PK), locations, name}, TUTOR {id (PK), name, officeid (FK references office (id)), experience}, STUDENTS {id (PK), name, scholarship, registereddate, tutorid (FK references tutor (id))}.

• select s.name, s.scholarship, o.name from office o join tutor t on o.id=t.officeid join students s on t.id=s.tutorid where s.scholarship between 4000 and 5000;

• select s.name, s.scholarship, o.name from office o join tutor t on o.id=t.officeid join students s on t.id=s.tutorid where s.scholarship 4000 and 5000;

• select s.name, s.scholarship, o.name from tutor t join students s on t.id=s.tutorid where s.scholarship between 4000 and 5000;

• select s.name, s.scholarship, o.name from tutor t join students s on t.id=s.tutorid where s.scholarship >=4000