sql Flashcards
What does SQL stand for?
Structured Query Language
What is SQL used for?
Create, maintain, and retrieve a relational database.
What does DDL stand for?
Data Definition Language.
What is DDL used for?
To define the structure of the database. e.g. CREATE TABLE, ADD COLUMN, DROP COLUMN, etc.
What does DML stand for?
Data Manipulation Language.
What is DML used for?
To extract the data from the relations. e.g. SELECT
What form do most SQL queries use?
SELECT L
FROM R
WHERE C
What does the FROM command do?
Specifies which table to select or delete data from.
What is the WHERE clause?
The condition part of a query. Tuples must satisfy the condition in order to match the query.
What does the SELECT clause do in a query?
Tells which attributes of the tuples matching the condition are produced as part of the answer.
What does the keyword AS do?
Allows an alias to be used for column headers. Used in the SELECT clause. e.g. SELECT title AS name.
What are the six comparison operators used in the WHERE clause?
= (equality), <> (not equal to), , <=, >=
What is the concatenation operator in SQL?
||
What is an alternative form of string comparison?
s LIKE p, where s is a string and p is a pattern.
What does % in p do in s LIKE p?
% in p can match any sequence of 0 or more characters in s.
What does _ in p do in s LIKE p?
_ in p matches any one character in s.
What does the ORDER BY clause do?
Presents the tuples produced by a query in sorted order.
What is the default order of ORDER BY?
asc
What does ORDER BY follow in a query?
The WHERE clause or the optional GROUP BY and HAVING clauses.
What does the ADD keyword do?
Adds a column to an existing table. Used with ALTER TABLE. ADD (column1 datatype, column2 datatype)
What does ADD CONSTRAINT do?
Adds a constraint (e.g. PRIMARY KEY) to an existing table. ADD CONSTRAINT constraintName constraint (col1, col2,)
What are the types of constraints available in SQL?
NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT
What are constraints in SQL?
Rules that apply to the data types in a table.
What is the NOT NULL constraint?
Constraint that ensures a column cannot have a NULL value.
What is the UNIQUE constraint?
Constraint that ensures all values in a column are different.
What is the PIMARY KEY constraint?
A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.
What is the FOREIGN KEY constraint?
Constraint that uniquely identifies a record in another table.
What is the CHECK constraint?
Constraint that ensures all values in a column satisfy a specific condition.
What is the DEFAULT constraint?
Constraint that sets a default value for a column when no value is specified.
What does the ALTER TABLE command do?
Used with ADD, DELETE, DROP or MODIFY to add, delete, or modify columns in a table.
What does the ALTER COLUMN command do?
Changes the data type of a column in a table.
What does the ALL operator do?
Returns TRUE if all of subquery values meet some condition.
What must the ALL operator be preceded by?
Comparison operators – SELECT, WHERE, HAVING statements.
What is AND used with?
The WHERE clause to only include rows where both conditions are true.
What does the ANY operator do?
Compares a value to each value in a list or results from a query and evaluates to true if the result of an inner query contains at least one row.
What statements must the ANY operator be preceded by?
WHERE
What does the BETWEEN command do?
Selects values within a given range (inclusive). The values can be numbers, text, or dates. Can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
What is the CASE command used for?
Create different outputs based on conditions.
What are the two valid ways of using CASE statements?
- CASE foo
WHEN ‘bar’ THEN do something - CASE
WHEN some condition THEN do something
What does the CHECK constraint do?
Specifies a predicate that every tuple must satisfy in a given relation. It limits the values that a column can hold in a relation.
What is the CREATE DATABASE command used for?
Create a new SQL database.
What is the CREATE INDEX command used for?
Creating indexes in tables (allows duplicate values).
What does the CREATE TABLE command do?
Creates a new table in a database.
What does the CREATE PROCEDURE command do?
Create a stored procedure.
What does the CREATE UNIQUE INDEX command do?
Creates a unique index on a table (no duplicate values allowed).
What does the CREATE VIEW command do?
Creates a view – a virtual table based on the result set of an SQL statement.
What does the default constraint do?
Provides a default value for a column.
What does the DELETE command do?
Delete existing records in a table.
What does the DISTINCT command do?
Returns only distinct (different) values in the result set. Used in SELECT.
What does the EXEC command do?
Executes a stored procedure.
What does the EXISTS command do?
Checks whether the result of a correlated nested query is empty or not. Can be used in a SELECT, UPDATE, INSERT or DELETE statement.
What is a FOREIGN KEY?
A field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
What does FULL JOIN do?
Creates a result set by combining results of both LEFT JOIN and RIGHT JOIN. The rows which there is no matching will contain NULL values.
What does the GROUP BY command do?
Arranges identical data into groups with the help of some functions.
What are three important things to remember about GROUP BY?
Used with the SELECT statement.
Placed after the WHERE clause.
Placed before ORDER BY.
What does the HAVING command do?
Result filter that works on aggregate data. Used instead of WHERE and acts as a post-filter. Typically comes after a GROUP BY.
What does the IN operator do?
Tests if the expression matches any value in the list of values. It is used to remove the need of multiple OR condition in SELECT, INSERT, UPDATE or DELETE.
What does the INNER JOIN command do?
Selects all rows from both the tables that satisfy some condition.
What does the INSERT INTO command do?
Inserts new rows in a table.
What does the IS NULL command do?
Tests for empty values.
What does the LEFT JOIN command do?
Returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join.
What does the LIMIT command do?
Sets an upper limit on the number of tuples returned by a query.
What does the NOT operator do?
Includes rows where a condition is not true. Used in WHERE clause.
What does the OR operator do?
Includes rows where either condition is true. Used in WHERE clause.
What does the PRIMARY KEY constraint do?
Uniquely identifies each record in a table.
What does the RIGHT JOIN command do?
Returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join.
What does the SELECT INTO command do?
Copies data from one table and inserts it into a new table.
What does the SELECT TOP command do?
Specifies the number of records to return.
What does the SET command do?
Used with UPDATE to specify which columns and values should be updated in a table.
What does the UNION command do?
Combines the result set of two or more SELECT statements (only distinct values).
What does the UNION ALL command do?
Combines the result set of two or more SELECT statements (allows duplicate values).
What does the UPDATE command do?
Updates existing rows in a table.
What does the VALUES command do?
Specifies the values of an INSERT INTO statement.
