SQL - Basics II

Question 1

The ORDER BY clause is used to sort the rows. What is the syntax for this?

Answer 1

SELECT column_name(s) FROM table_name
ORDER BY column_name(s)

Question 2

What keyword would you use if you want to sort values in descending order?

Answer 2

DESC

Question 3

What keyword would you use if you want to sort values in ascending order?

Answer 3

ASC

Question 4

AND and OR join two or more conditions in a WHERE clause.

The AND operator displays a row if ___ conditions listed are true.

Answer 4

all

Question 5

AND and OR join two or more conditions in a WHERE clause.

The OR operator displays a row if ___ of the conditions listed are true.

Answer 5

any

Question 6

AND (and OR - see hint) join two or more conditions in a WHERE clause.

What is the syntax of these statements?

Answer 6

SELECT column_name FROM table_name
WHERE columname’value’
AND columname’value’

Question 7

The IN operator may be used if you know the exact value you want to return for at least one of the columns. What is the syntax for this expression?

Answer 7

SELECT column_name FROM table_name

WHERE column_name IN (value1,value2,..)

Question 8

The BETWEEN … AND operator selects a range of data between two values. These values can be numbers, text, or dates. What is the syntax for this expression?

Answer 8

SELECT column_name FROM table_name
WHERE column_name
BETWEEN value1 AND value2

Question 9

To display the persons outside the range used in a BETWEEN … AND statement, use the ___ operator.

Answer 9

NOT

Question 10

With SQL, aliases can be used for column names and table names. What is the syntax for Column Name Alias?

Answer 10

SELECT column_name AS column_alias FROM table_name

Question 11

With SQL, aliases can be used for column names and table names. What is the syntax for Table Name Alias?

Answer 11

SELECT column_name FROM table_name AS table_alias

Question 12

Tables in a database can be related to each other with keys. A primary key is a column with a ______ _____ for each row.

Answer 12

unique value

Question 13

We can select data from two tables by referring to two tables. If we had one table named Employees which had columns named Employee_ID(primary key) and Name and another table called Orders that had columns named Prod_ID(primary key), Product, and Employee_ID how would we find out who has ordered what product?

Answer 13

SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID

Question 14

We can select data from two tables by referring to two tables. If we had one table named Employees which had columns named Employee_ID(primary key) and Name and another table called Orders that had columns named Prod_ID(primary key), Product, and Employee_ID how would we find out who ordered a printer?

Answer 14

SELECT Employees.Name
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID
AND Orders.Product=’Printer’

Question 15

We can select data from two tables with the INNER JOIN keyword. The INNER JOIN returns all rows from both tables where there is a match. If there are rows in table_name1 that do not have matches in table_name2, those rows will not be listed. What is the syntax for this?

Answer 15

SELECT table_name1.column_name, table_name2.column_name
FROM table_name1
INNER JOIN table_name2
ON table_name1.primary_key1 = table_name2.primary_key2

Question 16

We can select data from two tables with the LEFT JOIN keyword. The LEFT JOIN returns all the rows from table_name1, even if there are no matches in table_name2. If there are rows in table_name1 that do not have matches in table_name2, those rows also will be listed. What is the syntax for LEFT JOIN?

Answer 16

SELECT table_name1.column_name, table_name2.column_name
FROM table_name1
LEFT JOIN table_name2
ON table_name1.primary_key1 = table_name2.primary_key2

Question 17

We can select data from two tables with the RIGHT JOIN keyword. The RIGHT JOIN returns all the rows from table_name2, even if there are no matches in table_name1. If there had been any rows in table_name2 that did not have matches in table_name1, those rows also would have been listed. What is the syntax for RIGHT JOIN?

Answer 17

SELECT table_name1.column_name, table_name2.column_name
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.primary_key1 = table_name2.primary_key2

Question 18

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the ____ data type.

Answer 18

same

Question 19

The UNION command is used to select related information from two tables, much like the JOIN command. With UNION, only distinct values are selected. What is the syntax for UNION?

Answer 19

SQL Statement 1
UNION
SQL Statement 2

Question 20

The _____ ___ command is equal to the UNION command, except that it selects all values.

Answer 20

UNION ALL

Question 21

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. What is the syntax for UNION ALL?

Answer 21

SQL Statement 1
UNION ALL
SQL Statement 2

Question 22

What is the syntax used to create a new database?

Answer 22

CREATE DATABASE database_name

Question 23

What is the syntax for creating a new table in a database?

Answer 23

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
.......
)

Question 24

In the example below, what does the numbers in parenthases indicate?

CREATE TABLE Person
(
LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3)
)

Answer 24

The maximum length for the column.

Question 25

The data type for creating a table with specifies what type of data the column can hold. What are the four integer values?

Answer 25

integer(size)
int(size)
smallint(size)
tinyint(size)

Question 26

The data type for creating a table with specifies what type of data the column can hold. What are the two most common data types for decimal numbers?

Answer 26

decimal(size,d)

numeric(size,d)

Question 27

The data type for creating a table with specifies what type of data the column can hold. What is the type used for a fixed length string (can contain letters, numbers, and special characters).

Answer 27

char(size)

Question 28

The data type for creating a table with specifies what type of data the column can hold. What is the data type for a variable length string (can contain letters, numbers, and special characters).

Answer 28

varchar(size)

Question 29

The data type for creating a table with specifies what type of data the column can hold. What is the data type used to hold a date?

Answer 29

date(yyyymmdd)

Question 30

You can create an _____ in an existing table to locate rows more quickly and efficiently. It is possible to create one on one or more columns of a table, and each is given a name. The users cannot see them, they are just used to speed up queries.

Answer 30

index

Question 31

A unique index means that two rows cannot have the same index value. What is the syntax for a unique index?

Answer 31

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Question 32

A simple index, one without the UNIQUE keyword, allows duplicate values. What is the syntax for a simple index?

Answer 32

CREATE INDEX index_name
ON table_name (column_name)

Question 33

You can delete an existing index in a table with the ____ _____ statement.

Answer 33

DROP INDEX

Question 34

You can delete an existing index in a table with the DROP INDEX statement.

What is the syntax for Microsoft SQLJet (and Microsoft Access)?

Answer 34

DROP INDEX index_name ON table_name

Question 35

You can delete an existing index in a table with the DROP INDEX statement.

What is the syntax for MS SQL Server?

Answer 35

DROP INDEX table_name.index_name

Question 36

You can delete an existing index in a table with the DROP INDEX statement.

What is the syntax for MySQL?

Answer 36

ALTER TABLE table_name DROP INDEX index_name

Question 37

To delete a table (the table structure, attributes, and indexes will also be deleted) you would use what syntax?

Answer 37

DROP TABLE table_name

Question 38

To delete a database what syntax would you use?

Answer 38

DROP DATABASE database_name

Question 39

If you only want to get rid of the data inside a table, and not the table itself, use the TRUNCATE TABLE command. What is the syntax for this command?

Answer 39

TRUNCATE TABLE table_name

Question 40

The _____ _____ statement is used to add or drop columns in an existing table.

Answer 40

ALTER TABLE

Question 41

The ALTER TABLE statement is used to add or drop columns in an existing table. What is the ADD command syntax?

Answer 41

ALTER TABLE table_name

ADD column_name datatype

Question 42

The ALTER TABLE statement is used to add or drop columns in an existing table. What is the DROP COLUMN command syntax?

Answer 42

ALTER TABLE table_name

DROP COLUMN column_name

Question 43

What is the syntax for built-in SQL functions?

Answer 43

SELECT function(column) FROM table

Question 44

What are two basic types of functions in SQL?

Answer 44

Aggregate Functions and Scalar functions

Question 45

GROUP BY… was added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called, and without the GROUP BY function it was impossible to find the sum for each individual group of column values. What is the syntax for this command?

Answer 45

SELECT column_name1,SUM(column_name2) FROM table GROUP BY column_name1

Question 46

HAVING… was added to SQL because the WHERE keyword could not be used against aggregate functions (like SUM), and without HAVING… it would be impossible to test for result conditions.

The syntax for the HAVING function is?

Answer 46

SELECT column,SUM(column) FROM table
GROUP BY column
HAVING SUM(column) condition value

Question 47

The SELECT INTO statement is most often used to create backup copies of tables or for archiving records. What is the syntax for this statement

Answer 47

SELECT column_name(s) INTO newtable [IN externaldatabase]
FROM source

Question 48

What is a view in SQL?

Answer 48

A VIEW is a virtual table based on the result-set of a SELECT statement.

Question 49

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from a single table. What is the syntax for a VIEW statement?

Note: The database design and structure will NOT be affected by the functions, where, or join statements in a view.

Answer 49

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition