6 SQL Flashcards by Jimothy Todd

What is SQL?

SQL is the language used to talk to databases.

How well did you know this?

Not at all

Perfectly

What are the two main types of SQL statements?

DDL - Data Definition Language
DML - Data Manipulation Language

How well did you know this?

Not at all

Perfectly

In SQL, what is a table referred to as?

Relation

How well did you know this?

Not at all

Perfectly

In SQL, what is a row referred to as?

Tuple

How well did you know this?

Not at all

Perfectly

In SQL, what is a column referred to as?

Attribute

How well did you know this?

Not at all

Perfectly

What does an SQL schema include?

Schema name
User (account) who owns the schema (authorization identifier)
List of descriptions of what is inside (tables, views, etc)

How well did you know this?

Not at all

Perfectly

How do you create an SQL schema named Company owned by user Fury?

CREATE SCHEMA Company AUTHORIZATION Fury;

How well did you know this?

Not at all

Perfectly

What is a catalog in SQL?

A named collection of schemas in an SQL environment.

How well did you know this?

Not at all

Perfectly

How to create a new table called employee inside the SQL schema called company?

CREATE TABLE Company.EMPLOYEE(
* FName VARCHAR(15) NOT NULL,
* LName VARCHAR(15) NOT NULL,
* SSN CHAR(10) NOT NULL,
* NIE CHAR(10) NOT NULL,
* BDate DATE,
* Address VARCHAR(30),
* Salary DECIMAL,
* BossSSN CHAR(10),
* NumDept INT NOT NULL,
PRIMARY KEY (SSN),
UNIQUE (NIE),
FOREIGN KEY (NumDept) REFERENCES DEPARTMENT(DNumber),
FOREIGN KEY (BossSSN) REFERENCES EMPLOYEE(SSN)
);

How well did you know this?

Not at all

Perfectly

What does the UNIQUE constraint do in SQL?

Defines an alternate key.

How well did you know this?

Not at all

Perfectly

What does VARCHAR mean in SQL?

Adjusts to actual number of characters but max number is set in ().

How well did you know this?

Not at all

Perfectly

What does CHAR mean in SQL?

Fixed number of characters, will be that number even if they use less.

How well did you know this?

Not at all

Perfectly

What does DECIMAL mean in SQL?

Stores numbers with fixed digits before and after the decimal.

How well did you know this?

Not at all

Perfectly

What does DATE mean in SQL?

Stores calendar dates in the format YYYY-MM-DD.

How well did you know this?

Not at all

Perfectly

What does INT mean in SQL?

Whole number, no decimals, commonly used for counting.

How well did you know this?

Not at all

Perfectly

What does NOT NULL mean in SQL?

Column must have value, can’t be left empty.

How well did you know this?

Not at all

Perfectly

How do you create a custom domain in SQL?

CREATE DOMAIN SSN-TYPE AS CHAR(10);

How well did you know this?

Not at all

Perfectly

How can you reuse a custom domain in SQL?

SSN SSN-TYPE; instead of SSN CHAR(10).

How well did you know this?

Not at all

Perfectly

What are SQL clauses?

Parts of an SQL query, e.g. SELECT and FROM.

How well did you know this?

Not at all

Perfectly

What does the SELECT clause do in an SQL query?

Specifies the list of attribute (column) names to be retrieved.

How well did you know this?

Not at all

Perfectly

What does the FROM clause do in an SQL query?

Specifies the list of relation (table) names necessary to process the query.

How well did you know this?

Not at all

Perfectly

What does the WHERE clause do in an SQL query?

Study These Flashcards

Specifies a Boolean expression.

What is prefixing in SQL?

Study These Flashcards

Putting the table name before the column name, e.g. DEPARTMENT.Number.

What are aliases in SQL?

Study These Flashcards

Temporary names for columns or tables used to make output or query easier to read or write

What does 'SELECT *' do in SQL?

Selects all attributes

What happens when there is no WHERE clause in a SQL query?

Selects all rows (tuples)

What does 'SELECT DISTINCT Column' do?

Removes duplicates of that column

What is the cost of duplicate elimination in SQL?

Expensive (sort + eliminate)

Which SQL operations can be used between queries to remove duplicates?

UNION, DIFFERENCE and INTERSECTION

What is a nested query?

Queries performed within the WHERE clause of another query called the outer query

What does the SQL operator 'IN' do?

Checks if a value matches any value in a list

How does SQL handle multiple nested queries with the same column name?

Uses the one from the innermost query first then works its way outward

What is an example of a single block query in SQL?

SELECT E.FName, E.LName FROM EMPLOYEE E, DEPENDENT D WHERE D.ESSN = E.SSN and E.LName = D.Name

List some SQL operators.

* IN * = * < * > * ≤ * ≥ * <> (same as !=) * ANY (or SOME) * ALL

What does the SQL operator 'ANY' do?

Checks if there exists at least one value in the set that satisfies the condition

What are NULL values in SQL?

Missing, undefined, or not applicable

What are some built-in SQL aggregation functions?

* SUM (SUM) * AVERAGE (AVG) * MAXIMUM (MAX) * MINIMUM (MIN) * COUNT

How do you apply aggregate functions to subgroups of tuples?

Using GROUP BY

What does the HAVING condition do in SQL?

Restricts groups based on a condition

What is the purpose of the ORDER BY clause in SQL?

To order tuples of the result of a query by the value of 1 or more attributes

What are the types of Joins in SQL?

* Inner Join * Left (Outer) Join * Right (Outer) Join * Full (Outer) Join * Cross (Cartesian) Join * Self Join

What does an Inner Join do?

Retrieves records that have matching values in the join columns of both tables

What is a Full (Outer) Join?

Returns all rows from both tables, including unmatched rows, filling in with NULL where there is no match

What is a Cross (Cartesian) Join?

Returns product of the two tables, combining each row from the first table with every row from the second table

What SQL commands are used for data manipulation?

* INSERT INTO * UPDATE * DELETE FROM

What does DDL stand for?

Data Definition Language

What are the three main commands in DDL?

* CREATE * ALTER * DROP

What is a view in SQL?

A virtual table defined by a query that pulls data from real tables when used

What are the characteristics of a view?

* Looks like a table * Doesn’t store data itself * Can be reused

When can a view be updated?

* Made from just one table * Doesn’t use GROUP BY or aggregate functions * Includes the primary key of the base table

When can a view NOT be updated?

* Based on joins * Uses GROUP BY or aggregation functions * Doesn’t include a unique identifier

What is query modification in the context of views?

System rewrites my update to apply directly to the base table(s)

What is view materialization?

SQL engine creates physical temporary table from the view, keeping it updated

6 SQL Flashcards

(53 cards)