Databases
Which SQL functions is used to count the number of rows in a SQL query? COUNT() CARDINAL() NUMBER() SUM()
COUNT()
Which would add a new DATABASE named db? CREATE DATABASE db; CREATE db FROM NEW DATABASE; CREATE db; CREATE NEW DATABASE db;
CREATE DATABASE db;
The flowing tables are used for this question: Customers 1 Bob 2 Bill 3 Bart Balances 2 100 3 200 4 300 ======================================= Alpha Result 2 Bill 100 3 Bart 200 4 NULL 300 Beta Result 1 Bob NULL 2 Bill 100 3 Bart 200 4 NULL 300 Cappa Result 1 Bob NULL 2 Bill 100 3 Bart 200 Delta Result 2 Bill 100 3 Bart 200 Customers LEFT JOIN Balances
Cappa
Which would remove a database table? DELETE TABLE table; REMOVE TABLE table; ZAP TABLE table; DROP TABLE table;
DROP TABLE table;
What does follow after the SQL WHERE clause?
Definition of the condition to be met for the rows to be returned.
(SELECT ______ FROM ______) UNION ALL (SELECT ______ FROM ______);
Relational Addition
Which of the following SQL statements is correct? SELECT FROM Sales WHERE Date BETWEEN '10/12/2005' AND '01/01/2006' SELECT SALES between '10/12/2005' and '01/01/2006' SELECT FROM Sales WHERE Date BETWEEN ('10/12/2005', '01/01/2006') SELECT * FROM Sales WHERE Date BETWEEN '10/12/2005' AND '01/01/2006'
SELECT * FROM Sales WHERE Date BETWEEN '10/12/2005' AND '01/01/2006'
Which of the following SQL statements has correct syntax? SELECT * FROM Table1 WHERE Column1 >= 10 SELECT * FROM Table1 WHERE Column1 = = 10 SELECT * FROM Table1 WHERE Column1 => 10 SELECT FROM Table1 WHERE Column1 => 10
SELECT * FROM Table1 WHERE Column1 >= 10
What does the following SQL statement do: SELECT Customer, COUNT(Order) FROM Sales GROUP BY Customer HAVING COUNT(Order) >5 ;
Selects all customers from table Sales that have made more than 5 orders.
What does the FROM SQL keyword specify?
The FROM SQL keyword specifies the tables
What does the HAVING clause do?
The HAVING keyword specifies a search condition for an aggregate
The difference between the DELETE and TRUNCATE SQL clauses is:
The TRUNCATE clause deletes all rows in a database table, while the DELETE clause can have a WHERE condition and might or might not delete all rows in a table.
Which SQL keyword is used to eliminate duplicate fields from the SELECT output? DIFFERENT DISTINCT UNIQUE DISTINCTIVE
DISTINCT
What does DBMS stand for?
Database Management System
RDBMS stands for...
Relational Database Management System
SELECT _______ FROM _____ WHERE NOT EXISTS (SELECT _____ FROM _____ WHERE NOT EXISTS (SELECT _____ FROM ____ WHERE _____=______));
Relational Division
(SELECT ______ FROM ______) UNION (SELECT _____ FROM ______);
Relational union
What is the MYSQL privilege used by the GRANT command to give a user READ-ONLY rights?
SELECT
Which of the following SQL statements selects the string 'Success'? SELECT Sucess SELECT LEFT('Success is all I need.', 15) SELECT 'Success' SELECT '%Success '
SELECT 'Success'
Which of the following statements gets the total value of the column 'Price' in the 'Sales' table? SELECT SUM(Price) FROM Sales SELECT ADD(Price) FROM Sales SELECT SUM(Price) WHERE Sales SELECT TOTAL(Price) WHERE Sales
SELECT SUM(Price) FROM Sales
Which of the following SQL statements is correct? SELECT Username, Password FROM Users HAVING Username = 'user1' SELECT Username, Password FROM Users SELECT Username, Password WHERE Username = 'user1' SELECT Username AND Password FROM Users
SELECT Username, Password FROM Users
What does the UNION ALL operator do?
The UNION ALL operator combines the results of two or more queries into a one result that includes all the rows from the queries in the union with duplicates
Which command should a use to change the value of rating to 'PG' for movie_id 23 in table movies CHANGE rating TO 23 FOR movie_id=23 IN movies; UPDATE movies SET rating="PG" WHERE movie_id=23; UPDATE movies rating="PG" WHERE movie_id=23; rating='PG';
UPDATE movies SET rating="PG" WHERE movie_id=23;
Which SQL keyword is used to specify conditional search? SEARCH FIND WHERE SELECT
WHERE
The AVG SQL function returns the ...
average in the values in a group.
The CREATE TABLE statement is used to...
create a new table
Which is correct? delete * from students; delete from table students; delete * from table students; delete from students;
delete from students;
The TRUNCATE TABLE...
deletes all rows from a table
Which of the following is correct? insert (first,last) values ('bob','nielson'); insert into customer (first,last) values ('bob','nielson'); insert into customer first,last values 'bob','nielson'; insert into first,last values 'bob','nielson';
insert into customer (first,last) values ('bob','nielson');
The flowing tables are used for this question: Customers 1 Bob 2 Bill 3 Bart Balances 2 100 3 200 4 300 ======================================= Alpha Result 2 Bill 100 3 Bart 200 4 NULL 300 Beta Result 1 Bob NULL 2 Bill 100 3 Bart 200 4 NULL 300 Cappa Result 1 Bob NULL 2 Bill 100 3 Bart 200 Delta Result 2 Bill 100 3 Bart 200 Customers RIGHT JOIN Balances
Alpha
The flowing tables are used for this question: Customers 1 Bob 2 Bill 3 Bart Balances 2 100 3 200 4 300 ======================================= Alpha Result 2 Bill 100 3 Bart 200 4 NULL 300 Beta Result 1 Bob NULL 2 Bill 100 3 Bart 200 4 NULL 300 Cappa Result 1 Bob NULL 2 Bill 100 3 Bart 200 Delta Result 2 Bill 100 3 Bart 200 Customers FULL JOIN Balances
Beta
Which is the the operator for the NOT equal to search condition? => <> >= >
<>
What is the correct operator when we want to specify greater or equal search condition?
>=
What is a trigger?
A trigger is executed when certain event occurs.
If you don't specify ASC or DESC after a SQL ORDER BY clause, the following is used by default:
ASC
The IN SQL keyword...
Determines if a value matches any of the values in a list or a sub-query.
What will be the result of the following SQL statement: SELECT LEFT('Have a nice day.', 4)
Have
Which of the following is a SQL aggregate function? LEFT CAST MIN RIGHT
MIN
SELECT ______ FROM ______ WHERE IN (SELECT ______ FROM ______);
Relational Intersection
SELECT _____ FROM ______, ______;
Relational Multiply
SELECT _____ FROM _____ WHERE NOT EXISTS (SELECT _____ FROM _____);
Relational Subtraction
Which SQL statement selects all rows from table called Contest, with column ContestDate having values greater or equal to May 25, 2006? SELECT * FROM Contest WHERE ContestDate > '05/25/2006' SELECT * FROM Contest WHERE ContestDate < '05/25/2006' SELECT * FROM Contest HAVING ContestDate >= '05/25/2006' SELECT * FROM Contest WHERE ContestDate >= '05/25/2006'
SELECT * FROM Contest WHERE ContestDate >= '05/25/2006'
The SQL BETWEEN operator ...
Specifies a range to test.
What is the difference between the WHERE and HAVING SQL clauses?
The WHERE SQL clause condition(s) is applied to all rows in the result set before the HAVING clause is applied (if present). The HAVING clause is used only with SELECT SQL statements and specifies a search condition for an aggregate or a group.
What will be the result of the following SQL statement: SELECT * FROM Table1 where Column1 > 10
The result will be all rows from Table1 which have Column1 values greater than 10.
What is the ABS SQL function used for?
To return the absolute positive value of a numeric expression.
The table rows are also known as...
Tuples
Order these in mySQL order and tell me what the third clause is GROUP BY SELECT WHERE FROM
WHERE
DELETE FROM table WHERE column=value; Would this statement execute without error?
YES
Can you use both SELECT and WHERE SQL clauses in one SQL statement?
Yes
Normalization is...
removing ambiguity and redundancy
There are triggers for...
update, delete and insert
What is a view?
A view is a virtual table
Which of the following SQL clauses is used to DELETE data from a database table? REMOVE CLEAR DROP DATA DELETE
DELETE
Which of the following is used to change data in a table? update modify edit correct
update
If A={1,2,3] and B={2,3,4} What would A add B be?
{1,2,2,3,3,4}
If A={1,2,3] and B={2,3,4} What would A union B be?
{1,2,3,4}
If A={1,2,3] and B={2,3,4} What would A subtract B be?
{1}
The flowing tables are used for this question: Customers 1 Bob 2 Bill 3 Bart Balances 2 100 3 200 4 300 ======================================= Alpha Result 2 Bill 100 3 Bart 200 4 NULL 300 Beta Result 1 Bob NULL 2 Bill 100 3 Bart 200 4 NULL 300 Cappa Result 1 Bob NULL 2 Bill 100 3 Bart 200 Delta Result 2 Bill 100 3 Bart 200 Customers EQUA JOIN Balances
Delta
The flowing tables are used for this question: Customers 1 Bob 2 Bill 3 Bart Balances 2 100 3 200 4 300 ======================================= Alpha Result 2 Bill 100 3 Bart 200 4 NULL 300 Beta Result 1 Bob NULL 2 Bill 100 3 Bart 200 4 NULL 300 Cappa Result 1 Bob NULL 2 Bill 100 3 Bart 200 Delta Result 2 Bill 100 3 Bart 200 Customers NATURAL JOIN Balances
Delta
Which SQL statement inserts data into a table called Projects? SAVE INTO Projects (ProjectName, ProjectDescription) VALUES ('Content Development', 'Website content development project') INSERT Projects ('Content Development', 'Website content development project') INSERT INTO Projects (ProjectName, ProjectDescription) VALUES ('Content Development', 'Website content development project') INSERT Projects VALUES ('Content Development', 'Website content development project')
INSERT INTO Projects (ProjectName, ProjectDescription) VALUES ('Content Development', 'Website content development project')
CREATE USER 'someuser'@'localhost' PASSWORD 'mypass'; Would this statement work?
NO
INSERT INTO table; (column-1, column-2, ... column-n) VALUES; (value-1, value-2, ... value-n); Will This work?
No
When inserting data in a table do you always have to specify a list of all column names you are inserting values for?
No