SQL Exam

A ____________ is an alternate name for a table in the FROM clause that can be used to reference the table.

Alias

To see what makes up the employees table, use the following command:

DESCRIBE employees;

The following are all privileges that can be assigned to a user on a database except:

FIND

A single statement in MySQL is terminated using the # symbol.

False

What is the most common type of join?

INNER JOIN

What does SQL stand for?

Structured Query Language

The GRANT command can be used to assign rights to a user in MySQL.

The GRANT command can be used to assign rights to a user in MySQL.

Database Normalization was first proposed by Edgar F. Codd.

True

The DROP command can be used to delete an entire table from a database.

True

Which statement is used to delete a single row of data from a database?

DELETE

A database is considered completely normalized when it is in

BCNF

Which operator is used to select values within a range?

BETWEEN

Which statement is used to add a new row into a database?

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 ('Simon','Johnson');

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 can you return the number of records in the "Persons" table?

SELECT COUNT(*) FROM Persons

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

SELECT FirstName FROM Persons;

All of the following can be performed in SQL except:

Send an email from a database

For two tables to be joined in a UNION, they must have the same number of columns and their corresponding columns have identical data types and lengths.

True

What statement is used to update data in a database?

UPDATE

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

DELETE FROM Persons WHERE FirstName = 'Peter';

Which SQL statement is used to return only different values?

SELECT DISTINCT

To use MySQL from a web browser, a tool such as phpMyAdmin can be used.

True

With SQL, how would you remove the column 'Gender' from the Persons table?

ALTER TABLE Persons DROP COLUMN Gender;

To make a new database named "Employees" in MySQL you would use which command:

CREATE DATABASE Employees;

Which SQL statement is used to create a table in a database?

CREATE TABLE

A ___________ is a structure that contains different categories of information and the relationships between these categories.

Database

A(n) ____________ is like a noun. This is a person, place, thing or event.

Entity

MySQL can only be installed using Linux operating systems.

False

MySQL is an open source software system owned and maintained by Microsoft.

False

SQL is a standard provided by the Association of Computing Machinery (ACM).

False

The NOT NULL constraint enforces a column to accept null values.

False

The SELECT statement can be used to change the structure of a table.

False

Select the three different types of outer joins:

Full outer join Left outer join Right outer join

The ___________ operator will create a temporary table containing all rows that are in BOTH tables.

Intersect

Which operator is used to search for a specified pattern in a column?

LIKE

The ___________ operator creates a temporary table containing all of the rows that are in the first table, but that are not in the second.

Minus

Which SQL statement is used to extract data from a database?

SELECT

With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?

SELECT * FROM Persons ORDER BY FirstName DESC;

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 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 "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

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

With SQL, how do you select all the columns from a table named "Persons"?

SELECT * FROM Persons;

If you wanted to see what databases existed in your running instance of MySQL on a particular server you would use which command?

SHOW DATABASES;

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

True

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

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

To make MySQL utilize the database called products you would use which command?

USE products;

A ___________ returns every row that is in either the first table or the second table or both.

Union

Suppose we keep track of employee email addresses, and we only track one email address for each employee. Suppose each employee is identified by their unique employee number. We say email address is _____________________ on employee number

functionally dependant

Normalization is a process that _____________ a database design by generating relations that are of higher normal forms.

improves

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

ORDER BY

RDBMS stands for:

Relational Database Management System

With SQL, how can you add a column called "Gender" to the Persons table?

ALTER TABLE Persons ADD COLUMN Gender CHAR(1);

Table: studentinfo studentid firstname lastname gender dob 8675309 Jenny Tutone F 08/08/1988 8657309 Tommy Tutone M 08/09/1988 8675310 Janet Green F 08/08/1998 8675309 James Brown M 08/08/1988 Given the table above, what command would correctly return anyone with a birthday of August 8, 1988.

SELECT * FROM studentinfo WHERE dob='1988-08-08';

We say a relation is in 1NF if :

There are no repeating groups: two columns do not store similar information in the same table. The values in each column of a table are atomic (No multi-value attributes allowed). Each table has a primary key: minimal set of attributes which can uniquely identify a record