DFC20123 - DDT2B

STUDENT (SID, StudentName, Major, AdvisorID) ADVISOR (AdvisorID, AdvisorName, Office, Phone) Given the relations above, such that each student is assigned to one advisor, which of the following is true?

AdvisorID is a foreign key.

What does the CREATE TABLE statement do?

Creates a new database

Which of the following commands is used to delete data from a SQL database table?

DELETE

Which of the following is the correct SQL statement to use to remove rows from a table?

DELETE

With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?

DELETE FROM Persons WHERE FirstName = 'Peter'

SELECT DISTINCT is used if a user wishes to see duplicate columns in a query. (t/f)

False

The keyword LIKE can be used in a WHERE clause to refer to a range of values. (t/f)

False

Which of the following SQL clauses is used to enter data into a SQL table?

INSERT INTO

With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?

INSERT INTO Persons (LastName) VALUES ('Olsen')

With SQL, how can you insert a new record into the "Persons" table?

INSERT INTO Persons VALUES ('Jimmy', 'Jackson')

Given the table STUDENT(StudentID,Name,Advisor), which of the following SQL statements would be used to add new student data to the STUDENT table?

INSERT INTO STUDENT VALUES (123,'JONES','SMITH');

Which SQL keyword is used to retrieve a minimum value?

MIN

To sort the results of a query use:

ORDER BY

Which SQL keyword is used to sort the result-set?

ORDER BY

Which of the following SQL clauses is used to sort a result set?

ORDER BY

Which of the following SQL commands is used to retrieve data?

SELECT

With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?

SELECT * FROM Persons WHERE FirstName LIKE 'a%'

With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?

SELECT * FROM Persons WHERE FirstName='Peter'

With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?

SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'

With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'

Which SQL Statement selects all rows from a table called Products and orders the result set by ProductID column?

SELECT * FROM Products ORDER BY ProductID

With SQL, how can you return the number of records in the "Persons" table

SELECT COUNT(*) FROM Persons

Which SQL statement is used to return only different values?

SELECT DISTINCT

With SQL, how do you select a column named "FirstName" from a table named "Persons"?

SELECT FirstName FROM Persons

What does the following SQL Statement do: SELECT Customer, COUNT(Order) FROM Sales GROUP BY Customer HAVING COUNT(Order)>5

Selects all customers from the table Sales that have made more than 5 orders

What does the SQL FROM clause do?

Specifies the tables to retrieve data from.

What does the ALTER TABLE clause do?

The SQL ALTER TABLE clause modifies a table definition by altering, adding, or deleting table columns and/or constraints.

SQL provides five built-in functions: COUNT, SUM, AVG, MAX, MIN. (t/f)

True

The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true (t/f)

True

The SQL statement: SELECT Number1 + Number 2 AS Total FROM NUMBER_TABLE; adds two numbers from each row together and lists the results in a column named Total. (t/f)

True

The format SELECT-FROM-WHERE is the fundamental framework of SQL SELECT statements. (t/f)

True

The keyword BETWEEN can be used in a WHERE clause to refer to a range of values. (t/f)

True

The result of a SELECT statement can contain duplicate rows (t/f)

True

In an SQL SELECT statement querying a single table, according to the SQL

92 standard the asterisk (*) means that: - all columns of the table are to be returned

How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?

UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'

Given the table STUDENT(StudentID, Name, Advisor), which of the following SQL statements would be used to change the value of the Advisor field to 'Smith' for all the rows in the STUDENT table?

UPDATE STUDENT SET Advisor = 'Smith';

The LIKE SQL keyword is used along with...

WHERE clause

The AVG SQL function returns the...

average in the values in the group

Which of the following data types used in SQL would define a numeric field of the pattern 99.99?

numeric(2,2)