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