Sql - Basics Ii

Front 1

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

Back 1

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

Hint 1

Separate multiple column names with commas.

Front 2

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

Back 2

DESC

Front 3

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

Back 3

ASC

Front 4

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

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

Back 4

all

Front 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.

Back 5

any

Front 6

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

What is the syntax of these statements?

Back 6

SELECT column_name FROM table_name
WHERE columname<operator>'value'
AND columname<operator>'value'

Hint 6

To use OR, the syntax is the same - simply substitute OR in place of AND.

Front 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?

Back 7

SELECT column_name FROM table_name
WHERE column_name IN (value1,value2,..)

Front 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?

Back 8

SELECT column_name FROM table_name
WHERE column_name
BETWEEN value1 AND value2

Hint 8

IMPORTANT NOTE: The BETWEEN...AND operator is treated differently in different databases. Be sure to check how it is handled before using it.

Front 9

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

Back 9

NOT

Front 10

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

Back 10

SELECT column_name AS column_alias FROM table_name

Front 11

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

Back 11

SELECT column_name FROM table_name AS table_alias

Front 12

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

Back 12

unique value

Front 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?

Back 13

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

Front 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?

Back 14

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

Front 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?

Back 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

Front 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?

Back 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

Front 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?

Back 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

Front 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.

Back 18

same

Front 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?

Back 19

SQL Statement 1
UNION
SQL Statement 2

Front 20

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

Back 20

UNION ALL

Front 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?

Back 21

SQL Statement 1
UNION ALL
SQL Statement 2

Front 22

What is the syntax used to create a new database?

Back 22

CREATE DATABASE database_name

Front 23

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

Back 23

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

Front 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)
)

Back 24

The maximum length for the column.

Front 25

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

Back 25

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

Hint 25

The maximum number of digits are specified in parenthesis.

Front 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?

Back 26

decimal(size,d)
numeric(size,d)

Hint 26

The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d".

Front 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).

Back 27

char(size)

Hint 27

The fixed size is specified in parenthesis.

Front 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).

Back 28

varchar(size)

Hint 28

The maximum size is specified in parenthesis.

Front 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?

Back 29

date(yyyymmdd)

Front 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.

Back 30

index

Front 31

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

Back 31

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Front 32

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

Back 32

CREATE INDEX index_name
ON table_name (column_name)

Front 33

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

Back 33

DROP INDEX

Hint 33

Note: the synax for using this statement varies depending on which SQL language you are using.

Front 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)?

Back 34

DROP INDEX index_name ON table_name

Front 35

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

What is the syntax for MS SQL Server?

Back 35

DROP INDEX table_name.index_name

Front 36

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

What is the syntax for IBM DB2 and Oracle?

Back 36

DROP INDEX index_name

Front 37

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

What is the syntax for MySQL?

Back 37

ALTER TABLE table_name DROP INDEX index_name

Front 38

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

Back 38

DROP TABLE table_name

Front 39

To delete a database what syntax would you use?

Back 39

DROP DATABASE database_name

Front 40

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?

Back 40

TRUNCATE TABLE table_name

Front 41

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

Back 41

ALTER TABLE

Front 42

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

Back 42

ALTER TABLE table_name
ADD column_name datatype

Front 43

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

Back 43

ALTER TABLE table_name
DROP COLUMN column_name

Hint 43

Note: Some database systems don't allow the dropping of a column in a database table (DROP COLUMN column_name).

Front 44

What is the syntax for built-in SQL functions?

Back 44

SELECT function(column) FROM table

Front 45

What are two basic types of functions in SQL?

Back 45

Aggregate Functions and Scalar functions

Front 46

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?

Back 46

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

Front 47

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?

Back 47

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

Front 48

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

Back 48

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

Front 49

What is a view in SQL?

Back 49

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

Front 50

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.

Back 50

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