Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

88 cards

()

Section 1

Preview this deck

Three commands used to modify the database

Front

1 / 88

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 14, 2020

Cards (88)

Section 1

(50 cards)

Three commands used to modify the database

Front

INSERT DELETE UPDATE

Back

SQL's four sublanguages

Front

1. Data Definition Language(DDL) 2. Data Manipulation Language(DML) 3. Transaction Control Language(TCL) 4. Data Control Language(DCL)

Back

SQL Deletes

Front

We can delete rows from tables using the DELETE statement

Back

DDL Commands

Front

Create, Drop, Rename, Alter (Truncate)*

Back

Schema Creation

Front

SQL uses the CREATE SCHEMA statement to create new schema: CREATE SCHEMA COMPANY; Note that all SQL commands end in a semi-colon Only authorized users can create schemas Generally the DBA controls who can create schemas

Back

Aliases

Front

Aliases can also help us with circular references, or if two tables have the same name for attributes

Back

DCL Commands

Front

(Grant, Revoke)*

Back

Arithmetic in queries

Front

Can use all addition (+), subtraction (-), multiplication (*) and division (/) symbols

Back

SQL Retrieval with the SELECT statement

Front

SELECT <attributes> FROM <tables> [WHERE <condition>] [ GROUP BY <attribute(s)> ] [ HAVING <group condition> ] [ ORDER BY <attribute list> ]; <attributes> - Projection attributes <condition> - Selection condition

Back

Table

Front

Equivalent to relational model's relation

Back

String SQL Data Type

Front

1. CHAR(n)- fixed length character string (length n); only used when the exact length of text data is known, such as keys or hashes 2. VARCHAR(n)-variable length character string (up to length n); most efficient use of space for smaller text data

Back

Specifying Key and Referential Integrity Constraints- Foreign Key Clause

Front

Default operation: reject update on violation Attach referential triggered action clause Options include SET NULL, CASCADE, and SET DEFAULT Action taken by the DBMS for SET NULL or SET DEFAULT is the same for both ON DELETE and ON UPDATE CASCADE option suitable for "relationship" relations

Back

DROP command

Front

Used to drop named schema elements, such as tables, views, domains, or constraints

Back

Specifying Key and Referential Integrity Constraints- Primary Key Clause

Front

PRIMARY KEY clause- specifies one or more attributes that make up the primary key of a relation; Dnumber INT PRIMARY KEY;

Back

Set operations on queries

Front

UNION (R U S) INTERSECT (R ∩ S) EXCEPT (R - S)

Back

Specifying Attribute Constraints and Attribute Defaults

Front

NOT NULL- NULL is not permitted for a particular attribute Default value- DEFAULT <value>

Back

Naming Constraints

Front

Use keyword CONSTRAINT to name a constraint; Useful for later altering

Back

SQL Data Types

Front

Numeric String Boolean Date Time Time-stamp Date-time

Back

Update

Front

We can change values in the table using the UPDATE statement

Back

What if a SQL query is missing the WHERE clause?

Front

Where clauses are not required When missing, ALL results are returned: SELECT Fname, Lname FROM EMPLOYEE; This would return all employee names in the table

Back

Schema

Front

Identified by a schema name Can be thought of as a grouping of elements into one database idea: Tables, Constraints, Views, Domains, Authorizations (Roles)

Back

BOOLEAN SQL Data Type

Front

What you would expect - true vs. false Sometimes represented as a TINYINT (0 or 1)

Back

Value Wildcards: LIKE, %

Front

Sometimes we don't want to match a whole attribute completely % replaces an arbitrary number of zero or more characters We can use a wildcard in the WHERE clause with LIKE SELECT Fname, Lname FROM Employee WHERE Lname LIKE 'Smith%'; Will match any last name like Smith*, including Smith, Smithton, Smithfield, etc.

Back

SQL Inserts

Front

Basic statement: put values in in the same order as the CREATE TABLE statement used to create the table Requires that we know all of the values or use NULL for values we don't have Can use a modified version of INSERT to specify explicit attributes Anything not in the list is set to NULL (or default value if set), but be careful - at a minimum, all primary key values must be set in any insert

Back

DISTINCT

Front

Even on tables that have a primary key, sometimes queries will return duplicates. If we want to remove duplicates, we use the DISTINCT keyword

Back

What is considered one of the major reasons for the commercial success of relational databases?

Front

SQL standard

Back

Date SQL Data Type

Front

YYYY-MM-DD

Back

CREATE statement

Front

Used for data definition

Back

Specifying Basic Constraints in SQL

Front

Key and referential integrity constraints Restrictions on attribute domains and NULLs Constraints on individual tuples within a relation

Back

Column

Front

Equivalent to attribute

Back

Numeric SQL Data Type

Front

1. INT - integer Also SMALLINT (sometimes BIGINT/LONG) 2. REAL - floating point Also DOUBLE PRECISION, FLOAT

Back

DML Commands

Front

Update, Delete, Insert, Select

Back

How is SQL a comprehensive database language?

Front

Statements for data definition, queries and updates Defining views, specifying authorization Setting up integrity constraints and transaction controls

Back

Datetime SQL Data Type

Front

YYYY-MM-DD HH:MM:SS

Back

TCL Commands

Front

Commit, Rollback (Savepoint)*

Back

Value Wildcards: LIKE, _

Front

( _ ) replaces a single character Find all employees who were born during the 1950s SELECT Fname, Lname FROM Employee WHERE Bdate LIKE '_ _ 5 _ _ _ _ _ _ _';

Back

SQL

Front

Structured Query Language Core specification, plus specialized extensions High-level, declarative programming language User specifies what they want, not how to do it

Back

CREATE TABLE EMPLOYEE ( ... ); What goes in those parentheses?

Front

Attributes and data types

Back

Time SQL Data Type

Front

HH:MM:SS

Back

Table Creation

Front

CREATE TABLE COMPANY.EMPLOYEE ( ... ); Schema_Name.Table_Name creates a table in a specific schema Most relational databases allow you to specify your schema beforehand - MySQL: USE COMPANY; CREATE TABLE EMPLOYEE ( ... );

Back

Base tables (base relations)

Front

Relation and its tuples are actually created and stored as a file by the DBMS

Back

Domain

Front

Name used with the attribute specification Makes it easier to change the data type for a domain that is used by numerous attributes Improves schema readability CREATE DOMAIN SSN_TYPE AS CHAR(9);

Back

Specifying Key and Referential Integrity Constraints- Unique Clause

Front

UNIQUE clause- specifies alternate (secondary) keys; Dname VARCHAR(15) UNIQUE;

Back

The ALTER Command

Front

Alter table actions include: 1. Adding or dropping a column (attribute) 2. Changing a column definition 3. Adding or dropping table constraints Example: ALTER TABLE Company.Employee ADD COLUMN Job VARCHAR(12); To drop a column Choose either CASCADE or RESTRICT

Back

Virtual relations

Front

Created through the CREATE VIEW statement

Back

Attribute Wildcard: *

Front

Attribute wildcards in the SELECT clause retrieve all of the attributes

Back

Drop behavior options

Front

CASCADE and RESTRICT

Back

Timestamp SQL Data Type

Front

YYYY-MM-DD HH:MM:SS

Back

Specifying Constraints on Tuples Using CHECK

Front

CHECK clauses at the end of a CREATE TABLE statement Apply to each tuple individually Example: Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21); CHECK (Dept_create_date <= Mgr_start_date);

Back

Row

Front

Equivalent to tuple

Back

Section 2

(38 cards)

UPDATE Employee SET Salary = Salary * 1.1 WHERE EMPNO IN (SELECT EMPNO FROM Candidates);

Front

Give a 10% raise to all employees in Employee, whose employee number appears in the Candidates table.

Back

SELECT Dname, avg(Salary) FROM Employee E, Department D WHERE E.Dno = D.Dnumber GROUP BY Dname;

Front

List department names and the average salary of each department

Back

SELECT Dno, COUNT(*), AVG(Salary) FROM Employee GROUP BY Dno, Super_ssn;

Front

This would group together everyone who works in the same department and have the same manager

Back

Ordering

Front

We can force the query to come back in a particular order, very useful for reports and for debugging Use ORDER BY clause Keyword DESC to see result in a descending order of values Keyword ASC to specify ascending order explicitly SELECT*FROM Employee ORDER BY Lname DESC, Fname, Super_ssn;

Back

SELECT Dno, AVG(Salary) FROM Employee, (SELECT MIN(average) AS min_avg FROM (SELECT AVG(Salary) AS average FROM Employee GROUP BY DNO) ) AS MINA GROUP BY Dno HAVING AVG(Salary) > MINA.min_avg;

Front

Find all departments and their average salaries which aren't the lowest average in the company

Back

Add all employees whose commission is greater than half their salary to the CANDIDATES relation Given: CANDIDATES(EMPNO, NAME, DNO, SAL)

Front

INSERT INTO Candidates (SELECT EMPNO, Lname, Dno, Salary FROM Employee WHERE COMM > 0.5 * SAL);

Back

Aggregate Functions in SQL- Grouping

Front

Create subgroups of tuples before summarizing using the GROUP BY clause

Back

How to test for NULL values?

Front

Note that because NULL means "Unknown", we can't test for equality to NULL SELECT * FROM Employee WHERE Super_ssn = NULL; Instead we use "IS" or "IS NOT" when looking for NULL values SELECT * FROM Employee WHERE Super_ssn IS NULL;

Back

SELECT Lname, Fname FROM Employee EMP WHERE NOT EXISTS (SELECT * FROM Department DEPT WHERE EMP.Dno = DEPT.Dnumber AND LOC = 'Columbus' );

Front

List the names of employees who do not work in departments in Columbus

Back

WHERE Condition - Test For Existence

Front

EXISTS and NOT EXISTS are typically used in conjunction with a correlated nested query. Check whether the result of a correlated nested query is empty or not

Back

DELETE FROM Employee WHERE EMPNO = 561;

Front

Delete from Employee the employee with employee number 561.

Back

DELETE FROM Department DEPT WHERE (SELECT COUNT(*) FROM Employee WHERE Dno = DEPT.Dnumber) = 0;

Front

Delete from the Department table the departments having no employees.

Back

GROUP BY clause

Front

Specifies grouping attributes Aggregate functions are applied to each such group independently

Back

Three possible meanings of NULL

Front

Unknown value Withheld value Not Applicable value

Back

SELECT Essn FROM Works_On WHERE (Pno, Hours) IN (SELECT Pno, Hours FROM Works_On WHERE Essn='123456789');

Front

Find all employee SSN who work on the same project and same hours as employee '123456789'

Back

SELECT Lname, Fname FROM Employee EMP WHERE EXISTS (SELECT * FROM Department DEPT WHERE EMP.Dno = DEPT.Dnumber AND LOC = 'Columbus' );

Front

List the names of employees who work in departments in Columbus

Back

Views in SQL

Front

A view is a single table defined by other tables A virtual table Can be actual tables or other views

Back

Rewrite the following correlated nested queries as a single-block query: SELECT E.Fname, E.Lname FROM Employee AS E WHERE E.Ssn IN (SELECT Essn FROM Dependent AS D WHERE E.Fname = D.Dependent_name AND E.Sex = D.Sex);

Front

SELECT E.Fname, E.Lname FROM Employee AS E, Dependent AS D WHERE E.Ssn = D.Essn AND E.Sex = D.Sex AND E.Fname = D.Dependent_name;

Back

SELECT Dno, Dname, avg(Salary) FROM Employee EMP, Department DEPT, Dept_Locations DL WHERE EMP.Dno = DEPT.Dnumber AND DEPT.Dnumber = DL.Dnumber AND DL.Dlocation = 'Columbus' GROUP BY Dno, Dname HAVING avg(Salary) > 25000 ORDER BY 3 DESC;

Front

For all departments in Columbus with average salary > $25,000, list the department number, name, and average salary ordered by average salary in descending order

Back

Comparisons involving NULL

Front

Often not possible to tell which meaning is intended. SQL itself does not distinguish between different meanings for NULL (need to deal with that in applications). When making comparisons, SQL considers NULL to mean "Unknown"

Back

Use other comparison operators to compare a single value v

Front

= ANY (or = SOME) operator Returns TRUE if the value v is equal to some value in the set V and is hence equivalent to IN Other operators that can be combined with ANY (or SOME): >, >=, <, <=, and <>

Back

SELECT Dno, Dname FROM Employee E, Department D WHERE E.Dno = D.Dnumber GROUP BY Dno, Dname HAVING avg(Salary) < 25000;

Front

List departments (DNO, DNAME) in which the average employee salary < $25,000

Back

What should you use the NOT EXISTS function to check?

Front

Use NOT EXISTS function to check whether a nested query is empty

Back

Aggregate Functions in SQL

Front

Used to summarize information from multiple tuples into a single-tuple summary Built-in aggregate functions: COUNT, SUM, MAX, MIN, and AVG If NULLs exist in grouping attribute, separate NULL group is created Functions can be used in the SELECT clause

Back

Use join to dynamically create a table on the fly: SELECT Fname, Lname, Address FROM EMPLOYEE, DEPARTMENT WHERE Dno=Dnumber AND Dname='Research';

Front

SELECT Fname, Lname, Address FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber) WHERE Dname='Research';

Back

SELECT Ssn, Lname, 1.1 * Salary AS NEWSAL FROM Employee EMP, Department DEPT, Dept_Locations DL WHERE EMP.Dno = DEPT.Dnumber AND DEPT.Dnumber = DL.Dnumber AND Dlocation = 'Columbus';

Front

Show the result of giving everyone in departments in Columbus a 10% pay raise. List the employee number and name

Back

Comparison operator IN

Front

Compares value v with a set (or multiset) of values V Evaluates to TRUE if v is one of the elements in V

Back

What should you use the EXISTS function to check?

Front

Use the EXISTS function to check whether a nested query is not empty

Back

Nested queries

Front

Complete select-from-where blocks within WHERE clause of another query

Back

SELECT count(distinct Job) FROM Employee WHERE Dno = 50;

Front

How many different jobs are held by employees in department 50?

Back

HAVING clause

Front

Provides a condition on the summary information Functions can be used in the HAVING clause to select certain GROUPs

Back

How to use the CREATE VIEW command to create a new view in the database?

Front

Uses a SELECT statement to build the view, any SELECT statement can form the basis of a view Views are dynamic Not copies of the underlying tables Kept up-to-date with changes to the underlying tables Can be very efficient for joining commonly-used data Can store "virtual" values that only exist upon querying

Back

When an inner query references some attributes of a relation declared in an outer query, the queries are called what?

Front

Correlated Think of the nested query as being run once for each tuple in the outer query

Back

SELECT E.Fname, E.Lname FROM Employee AS E WHERE NOT EXISTS (SELECT * FROM Dependent AS D WHERE E.Ssn = D.Essn);

Front

Find all employees who do not have a dependent

Back

SELECT E.Fname, E.Lname FROM Employee AS E WHERE EXISTS (SELECT * FROM Dependent AS D WHERE E.Fname = D.Dependent_name AND E.Ssn = D.Essn AND E.Sex = D.Sex);

Front

Find all employees who have a dependent with the same first name and same sex as the employee

Back

SELECT Dno FROM Employee WHERE Job = 'Clerk' GROUP BY Dno HAVING count(*) > 10;

Front

List departments (numbers) that employ more than 10 clerks

Back

SELECT Dno, AVG(Salary) FROM Employee GROUP BY Dno HAVING AVG(Salary) > (SELECT MIN(average) FROM (SELECT AVG(Salary) AS average FROM Employee GROUP BY Dno));

Front

Find all departments and their average salaries which aren't the lowest average in the company

Back

Ranges: BETWEEN

Front

Ranges can be expressed as operators: WHERE Salary >= 30000 AND Salary <= 40000; Or using the BETWEEN keyword (range inclusive) WHERE Salary BETWEEN 30000 AND 40000;

Back