Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/50

Click to flip

50 Cards in this Set

  • Front
  • Back
  • 3rd side (hint)
The ORDER BY clause is used to sort the rows. What is the syntax for this?
SELECT column_name(s) FROM table_name
ORDER BY column_name(s)
Separate multiple column names with commas.
What keyword would you use if you want to sort values in descending order?
DESC
What keyword would you use if you want to sort values in ascending order?
ASC
AND and OR join two or more conditions in a WHERE clause.

The AND operator displays a row if ___ conditions listed are true.
all
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.
any
AND (and OR - see hint) join two or more conditions in a WHERE clause.

What is the syntax of these statements?
SELECT column_name FROM table_name
WHERE columname<operator>'value'
AND columname<operator>'value'
To use OR, the syntax is the same - simply substitute OR in place of AND.
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?
SELECT column_name FROM table_name
WHERE column_name IN (value1,value2,..)
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?
SELECT column_name FROM table_name
WHERE column_name
BETWEEN value1 AND value2
IMPORTANT NOTE: The BETWEEN...AND operator is treated differently in different databases. Be sure to check how it is handled before using it.
To display the persons outside the range used in a BETWEEN ... AND statement, use the ___ operator.
NOT
With SQL, aliases can be used for column names and table names. What is the syntax for Column Name Alias?
SELECT column_name AS column_alias FROM table_name
With SQL, aliases can be used for column names and table names. What is the syntax for Table Name Alias?
SELECT column_name FROM table_name AS table_alias
Tables in a database can be related to each other with keys. A primary key is a column with a ______ _____ for each row.
unique value
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?
SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID
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?
SELECT Employees.Name
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID
AND Orders.Product='Printer'
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?
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
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?
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
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?
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
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.
same
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?
SQL Statement 1
UNION
SQL Statement 2
The _____ ___ command is equal to the UNION command, except that it selects all values.
UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. What is the syntax for UNION ALL?
SQL Statement 1
UNION ALL
SQL Statement 2
What is the syntax used to create a new database?
CREATE DATABASE database_name
What is the syntax for creating a new table in a database?
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
.......
)
In the example below, what does the numbers in parenthases indicate?

CREATE TABLE Person
(
LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3)
)
The maximum length for the column.
The data type for creating a table with specifies what type of data the column can hold. What are the four integer values?
integer(size)
int(size)
smallint(size)
tinyint(size)
The maximum number of digits are specified in parenthesis.
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?
decimal(size,d)
numeric(size,d)
The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d".
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).
char(size)
The fixed size is specified in parenthesis.
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).
varchar(size)
The maximum size is specified in parenthesis.
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?
date(yyyymmdd)
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.
index
A unique index means that two rows cannot have the same index value. What is the syntax for a unique index?
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
A simple index, one without the UNIQUE keyword, allows duplicate values. What is the syntax for a simple index?
CREATE INDEX index_name
ON table_name (column_name)
You can delete an existing index in a table with the ____ _____ statement.
DROP INDEX
Note: the synax for using this statement varies depending on which SQL language you are using.
You can delete an existing index in a table with the DROP INDEX statement.

What is the syntax for Microsoft SQLJet (and Microsoft Access)?
DROP INDEX index_name ON table_name
You can delete an existing index in a table with the DROP INDEX statement.

What is the syntax for MS SQL Server?
DROP INDEX table_name.index_name
You can delete an existing index in a table with the DROP INDEX statement.

What is the syntax for IBM DB2 and Oracle?
DROP INDEX index_name
You can delete an existing index in a table with the DROP INDEX statement.

What is the syntax for MySQL?
ALTER TABLE table_name DROP INDEX index_name
To delete a table (the table structure, attributes, and indexes will also be deleted) you would use what syntax?
DROP TABLE table_name
To delete a database what syntax would you use?
DROP DATABASE database_name
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?
TRUNCATE TABLE table_name
The _____ _____ statement is used to add or drop columns in an existing table.
ALTER TABLE
The ALTER TABLE statement is used to add or drop columns in an existing table. What is the ADD command syntax?
ALTER TABLE table_name
ADD column_name datatype
The ALTER TABLE statement is used to add or drop columns in an existing table. What is the DROP COLUMN command syntax?
ALTER TABLE table_name
DROP COLUMN column_name
Note: Some database systems don't allow the dropping of a column in a database table (DROP COLUMN column_name).
What is the syntax for built-in SQL functions?
SELECT function(column) FROM table
What are two basic types of functions in SQL?
Aggregate Functions and Scalar functions
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?
SELECT column_name1,SUM(column_name2) FROM table GROUP BY column_name1
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?
SELECT column,SUM(column) FROM table
GROUP BY column
HAVING SUM(column) condition value
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
SELECT column_name(s) INTO newtable [IN externaldatabase]
FROM source
What is a view in SQL?
A VIEW is a virtual table based on the result-set of a SELECT statement.
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.
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition