Retrieve the lowest experience from tutor. 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 min (experience) from tutor;
select max (experience) from tutor;
select max (t.experience) from tutor t group by t.name;
select min (t.experience) from tutor t group by t.name;
Retrieve book name, number of books, price and publisher, for those books which have more than 20 copies. Tables : BOOK {bookid, title, publisher_name, price}. BOOKCOPIES {bookid, libraryid,noOfCopies [references Book (bookid)]}
select count (b.bookid),b.title, b.price, b.publisher_name from book b join book_copies bc on b.bookid=bc.bookid where bc.no_of_copies>20 group by b.title, b.price, b.publisher_name;
select count (b.bookid),b.title, b.price, b.publisher_name from book b join book_copies bc on b.bookid=bc.bookid where bc.no_of_copies>20;
select count (b.bookid),b.title, b.price, b.publisher_name from book b join book bc on b.bookid=bc.bookid where bc.no_of_copies>20 group by b.title, b.price, b.publisher_name;
select b.bookid,b.title, b.price, b.publisher_name from book b join book_copies bc on b.bookid=bc.bookid where bc.no_of_copies>20 group by b.title, b.price, b.publisher_name;
Retrieve card number, book name, borrower name, who have borrowed in 12 July, bookid (references Book (bookid)), libraryid (references Library (libraryid)), cardno 2013. Tables: BOOK {bookid, title, publisher_name, price}. BOOKLOANS {bookloans, cardno(references Borrower (cardno)),bookid (references Book (bookid)) dateout, duedate}. BORROWER {cardno, c_name, b_address, phoneno}
select bl.cardno, b.title, br.c_name from book b join bookloans bl on bl.bookid=b.bookid join borrower br on br.cardno=bl.cardno where bl.dateout='12-07-2013'
select bl.cardno, b.title, br.c_name from book b join bookloans bl on bl.bookid=b.bookid join borrower br on br.cardno=bl.cardno where bl.duedate='12-07-2013'
select bl.cardno, b.title, br.c_name from book b join bookloans bl on bl.bookid=b.bookid where bl.dateout='12-07-2013'
select bl.cardno, b.title, br.c_name from book b join borrower br on br.cardno=bl.cardno where bl.dateout='12-07-2013'
Retrieve max price for Fariza's publication. BOOK {bookid, title, publisher_name, price}.BOOK_AUTHORS {author_name, bookid (references Book (bookid))}
select max (b.price)from book b join book_authors ba on b.bookid=ba.bookid where ba.author_name = 'Fariza';
select max (b.price)from book b join book_authors ba on b.bookid=ba.bookid where ba.author_name = Fariza;
select max (b.price)from book b join book_authors ba b.bookid=ba.bookid where ba.author_name = 'Fariza';
select max (b.price)from book b join book_authors ba on b.bookid=ba.bookid; #subToPewDiePie
Retrieve book name, total number of book copies. BOOK {bookid, title, publisher_name, price}. BOOKCOPIES {bookid, libraryid,noOfCopies [references Book (bookid)]}
select b.title, sum (no_of_copies) from book b join book_copies bc on b.bookid=bc.bookid group by b.title;
select b.title, sum (no_of_copies) from book b join book_copies bc b.bookid=bc.bookid group by b.title;
select b.title, count(no_of_copies) from book b join book_copies bc on b.bookid=bc.bookid group by b.title;
select b.title, count(no_of_copies) from book b join book_copies bc b.bookid=bc.bookid group by b.title;
Retrieve book title and total number of book copies, which has id 1. BOOK {bookid, title, publisher_name, price}. BOOKCOPIES {bookid, libraryid,noOfCopies [references Book (bookid)]}
select b.title, sum (no_of_copies) from book b join book_copies bc on b.bookid=bc.bookid where b.bookid=1 group by b.title;
select b.title, sum (no_of_copies) from book b join book_copies bc on b.bookid=bc.bookid where b.bookid=1;
select b.title, count (no_of_copies) from book b join book_copies bc on b.bookid=bc.bookid where b.bookid=1 group by b.title;
select b.title, count (no_of_copies) from book b join book_copies bc on b.bookid=bc.bookid where b.bookid=1;
Retrieve borrower name and number of books that each borrower has. BOOKLOANS {bookloans, bookid (references Book (bookid)), libraryid (references Library (libraryid)), cardno (references Borrower (cardno)), dateout, duedate}. BORROWER {cardno, c_name, b_address, phoneno}
select br.c_name, count(bl.bookid) from borrower br join bookloans bl on br.cardno = bl.cardno group by br.c_name;
select br.c_name, count(bl.bookid) from borrower br join bookloans bl on br.cardno = bl.cardno;
select br.c_name, sum(bl.bookid) from borrower br join bookloans bl on br.cardno = bl.cardno group by br.c_name;
select br.c_name, sum(bl.bookid) from borrower br join bookloans bl on br.cardno = bl.cardno;
Retrieve books name and prices. Increase price twice more for books, which have more than 20 copies;BOOK {bookid, title, publisher_name, price}. BOOKCOPIES {bookid, libraryid,noOfCopies [references Book (bookid)]}
select b.title, b.price, b.price *2 as increasedprice from book b join book_copies bc on bc.bookid=b.bookid where bc.no_of_copies > 20;
select b.title, b.price book b join book_copies bc on bc.bookid=b.bookid where bc.no_of_copies > 20;
impossible to solve
select b.title, b.price, b.price *2 as increasedprice from book b join book_copies bc on bc.bookid=b.bookid;
Retrieve library name, book name, number of copies, which have more than 25 copies. BOOK {bookid, title, publisher_name, price}. BOOKCOPIES {bookid, libraryid,noOfCopies [references Book (bookid)]}. LIBRARY (libraryid)), cardno (references Borrower (cardno)), dateout, duedate}.
select l.libraryname, b.title, bc.no_of_copies from book b join book_copies bc on b.bookid=bc.bookid join library l on l.libraryid = bc.libraryid where bc.no_of_copies>25;
select l.libraryname, b.title, bc.no_of_copies from book b join book_copies bc on b.bookid=bc.bookid where bc.no_of_copies>25;
select l.libraryname, b.title, bc.no_of_copies from book join library l on l.libraryid = bc.libraryid where bc.no_of_copies>25;
select l.libraryname, b.title, bc.no_of_copies from book b join book_copies bc on b.bookid=bc.bookid join library l l.libraryid = bc.libraryid where bc.no_of_copies>25;
Retrieve the number of departments in the department table. Tables: DEPARTMENT {id, name}. EMPLOYEE {id, name, salary, dep_id (references Department (id))}
select count(id) from department
select sum (id) from department;
select coun(id) from department
select id from department
Retrieve total salaries payable to employee. Tables: DEPARTMENT {id, name}. EMPLOYEE {id, name, salary, dep_id (references Department (id))}
select sum (salary) from employee;
select count (salary) from employee;
select sum (id) from employee;
select max (salary) from employee;
Retrieve office name and tutor name, who have experience equal or more than five years. 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 >=5;
select o.name, t.name from office o join tutor t on o.id=t.officeid where t.experience >5;
select o.name, t.name from office o join tutor t o.id=t.officeid where t.experience >=5;
select o.name, t.name from office join tutor on o.id=t.officeid where t.experience >=5;
Retrieve tutor name and their office locations, who refer to CSSE and Management. 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, o.locations from office o join tutor t on o.id=t.officeid where o.name in ('CSSE', 'Management');
select t.name, o.locations from office o join tutor t on o.id=t.officeid where o.name in ('CSSE' or 'Management');
select t.name, o.locations from office o join tutor t o.id=t.officeid where o.name in ('CSSE', 'Management');
select t.name, o.locations from office o join tutor t on o.id=t.officeid where o.name in ('CSSE') or ('Management');
Retrieve student's name, which starts from A. 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 name from students where name like 'A_%';
select name from students where name like 'A';
select name from students where name like 'a%';
select name from students where name like '%A%'
Retrieve all students name with the department name, but excluding department CSSE. 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, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid where o.name <> 'CSSE';
select s.name, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid;
select s.name, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid where o.name = 'CSSE';
select s.name, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid group by o.name, s.name;
Which of the following query is incorrect for retrieving all students name with the department name, but excluding department CSSE. 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, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid where o.name <= 'CSSE';
select s.name, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid where o.name not like 'CSSE';
select s.name, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid where o.name not in ('CSSE');
Retrieve students name, scholarship and their office name, who have scholarship ranging from 3200 to 4500.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 students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid where s.scholarship between 3200 and 4500;
select s.name, s.scholarship, o.name from students s join tutor t on t.id=s.tutorid where s.scholarship between 3200 and 4500;
select s.name, s.scholarship, o.name from students join office o on o.id=t.officeid where s.scholarship between 3200 and 4500;
select s.name, s.scholarship, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid where s.scholarship 3200 and 4500;
What kind symbol is used to find one character in wildcards?
-
?
$
in
Find an average scholarship of students. Tables: STUDENTS {id (PK), name, scholarship, registereddate, tutorid }
select avg (scholarship) from students ;
select averg (scholarship) from students ;
select avrg (scholarship) from students ;
select av (scholarship) from students ;
Retrieve average scholarship of students for each department. 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 avg (scholarship), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid group by o.name;
select avg (scholarship), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid ;
select avg (scholarship), o.name from students s join tutor t on t.id=s.tutorid group by o.name;
select avg (scholarship), o.name from students s d join office o on o.id=t.officeid group by o.name;
Retrieve student number in each department. 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.id), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid group by o.name;
select sum (s.id), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid group by o.name;
select o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid group by o.name;
select count (s.id), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid;
select count (s.id), o.name from students s join tutor t
on t.id=s.tutorid join office o on o.id=t.officeid;
Retrieve total amount of scholarship of each department. 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 sum (scholarship), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid group by o.name;
select count (scholarship), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid group by o.name;
select sum (scholarship), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid;
select count (scholarship), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid;
SQL stands for
Sequence Question Language
Structured Query Language
Structured Querty Language
Selection Query Language
You can add a row using SQL in a database with which of the following
ADD
CREATE
INSERT
UPDATE
The command to remove rows from a table “Customer”
Remove from Customer
Delete from Customer
Drop from Customer
Update row from Customer
The SQL WHERE clause
Condition that limits the column data that are returned
Condition that limits the row data are returned
Clause that returns all rows
Clause that returns nothing
The command to eliminate a table from a database
Drop
Delete
Remove
Update
Which of the following is correct order of keywords for SQL Select statement?
SELECT, FROM, WHERE
WHERE, FROM, SELECT
FROM, WHERE, SELECT
SELECT, WHERE, FROM
SQL data definition commands make up a(n)
DDC
DML
DDL
DDD
In a relation, the columns are also called attributes
Find the SQL statement that is equal to: SELECT NAME FROM CUSTOMER WHERE STATE = 'VA';
SELECT NAME IN CUSTOMER WHERE STATE IN 'VA';
SELECT NAME IN CUSTOMER WHERE STATE = 'VA';
SELECT NAME FROM CUSTOMER WHERE STATE IN 'VA';
SELECT NAME IN CUSTOMER WHERE STATE = 'V';
In E\R diagrams, we will represent Entities as
Boxes with rounded corners
Links between two entities
Ovals
Diamond box
In E\R diagrams, we will represent Relationships as
In E\R diagrams, we will represent Attributes as
Many to many relationships are difficult to represent in database, so we need to
Split many to many relationship into two one to many relationships
Split one to many relationship into two one to many relationships
Split many to many relationship into one to many relationships
Split many to many relationship into three one to many relationships
The result of a SQL SELECT statement is a(n)
Report
Table
Form
File
Which of the following are the five built-in functions provided by SQL?
COUNT, SUM, AVG, MAX, MULT
SUM, AVG, MIN, MAX, NAME
SUM, AVG, MULT, DIV, MIN
COUNT, SUM, AVG, MAX, MIN
In a relation, the order of the rows matters
Given the functional dependency R → (S,T) , then it is also true that R → S
Given the functional dependency R → (S,T) , then it is also true that R → T
SQL can be used to
create database structures only
query database data only
modify database data only
All of the these can be done by SQL
The SQL statement that queries or reads data from a table is _
READ
QUERY
SELECT
NONE
A subquery in an SQL SELECT statement
can only be used with two tables
has a distinct form that cannot be duplicated by a join
can always be duplicated by a join
cannot have its results sorted using ORDER BY
The SQL keyword BETWEEN is used
for ranges
as a wildcard
to limit the columns displayed
None of the above
Sometimes you want to change the structure of an existing table, what are your options?
Change table
Drop Table
Alter Table
Create Table
how to Add a new column into existing table?
ALTER TABLE Student MODIFY COLUMN sDegree CHAR(64) NOT NULL
ALTER TABLE Student ADD COLUMN sDegree VARCHAR(64) NOT NULL
ALTER TABLE S ADD COLUMN s VACHAR(64) NOT NULL
ALTER TABLE Student DROP COLUMN sDegree VARCHAR(64) NOT NULL
how to rename a column in existing table?
ALTER TABLE Student RENAME COLUMN s to SS VACHAR(64) NOT NULL
how to change the row(s) in a table
insert
update
change
delete
how to remove the row(s) from a table
A SELECT statement can be nested inside another query to form a
Subselect
Subresults
Subquery
Query in query
SQL uses privileges to control access to tables and other database objects, so which is NOT?
Select privilege
Update privilege
Insert privilege
Drop privilege
To convert any relation into _______, split any nonatomic values
First normal form
Second normal form
Third normal form
Fourth normal form
A functional dependency (FD) is a
link between three sets of attributes in a relation
link between all sets of attributes in a relation
link between four sets of attributes in a relation
link between two sets of attributes in a relation
which if the following does not refer to redundancy problems?
INSERT anomalies
CREATE anomalies
UPDATE anomalies
DELETE anomalies
To convert any relation into _______, remove transitive dependency
What is it ?
Relation
Tuples
Attributes
Relationships
Choose the correct relational algebra operation
Union
Difference
Itersection
Product
Intersection
Are given table union compatible ?
yes
no
Which of the following is not correct? INSERT INTO Employee(ID, Name, Salary) VALUES
INSERT INTO Employee(ID, Name, Salary) VALUES(2,‘Mary’,26);
INSERT INTO Employee (Name,ID) VALUES (‘Mary’,2);
INSERT INTO Employee VALUES (2, ‘Mary’,26000);
INSERT INTO Employe VALUES(26, ‘Mary’,26);
Please increase salary for 10%?
UPDATE Employee SET Salary = Salary*0.5
UPDATE Employee SET Salary = Salary * 0.1
UPDATE Employee SET Salary = Salary * 1.1
UPDATE Employee Update Salary = Salary*0.1
Please remove staff, who earns more than 22000?
DELETE FROM Employee WHERE Salary >=22000
DELETE FROM Employe WHERE Salary => 22000;
REMOVE FROM Employee WHERE Salary >22000;
DELETE FROM Employee WHERE Salary = 22000;
πsName,sAddress(Student) is equal to?
SELECT Sname FROM Students
SELECT Sname, SAddress FORM Students
SELECT Sname and SAddress FROM Students
SELECT Sname, SAddress FROM Student
SQL query to find a list of the ID numbers and Marks for students who have passed IAI
Select ID, Mark from Grade Where code = 'IAI' and Mark > 50;
Select ID, Mark from Grade Where code = 'AIA' and Mark > 50;
Select ID, Mark, Code from Grade Where code = 'IAI';
Select ID, Mark from Grade Where code = 'IAI' and Mark >=50;
Find students who studying any Programming module
Select First,Last from Student,Grade Where Code='PR1'OR'PR2'
Select First,Last from Student Natural Join Grade Where Code like'PR%'
Select First Last from Student Natural Join Grade Where Code like'PR%'
Select First from Student Natural Join Grade Where Code = 'PR%'
How to use privileges in SQL?
ON<objects> TO<users> GRANT<privileges>
GRANT<privileges> ON<objects> TO<users>
GRANT<privileges> TO<users> ON<objects>
GRANT<tables> ON<objects> TO<users>
If Admin’ grants ALL privileges to ‘Manager’, and SELECT to ‘Finance’ with grant option, So..
‘Manager’ grants ALL to ‘Personnel’
‘Manager’ grants SELECT to ‘Personnel’
‘Finance’ grants SELECT to ‘Manager’
‘Finance’ grants ALL to ‘Manager’
‘Finance’ grants SELECT to ‘Personnel’
If ‘Manager’ revokes ALL from ‘Personnel’
‘Personnel’ still has ALL privileges from ‘Finance’
‘Finance’ still has SELECT privileges from ‘Personnel’
‘Personnel’ still has SELECT privileges from ‘Finance’
‘Admin’ still has ALL privileges from ‘Finance’
If ‘Finance revokes SELECT from ‘Personnel’
‘Personnel’ still has ALL privileges from ‘Manager’
If ‘Admin’ revokes Select from ‘Finance’
If ‘Admin’ revokes ALL from ‘Manager’
‘Finance still has SELECT privileges from ‘Admin’
Por favor espera - en proceso…