SQL Flashcards Preview

Databases > SQL > Flashcards

Flashcards in SQL Deck (20):
1

SQL Definition

High Level, declarative programming language

Specifies what the user wants, not how

2

Terms:

Table is a:
Row is a:
Column is a:

Table is a: relation
Row is a: tuple
Column is an attribute:

3

Table Creation in SQL:

CREATE TABLE STUDENT(
Fname VARCHAR(15) NOT NULL,
SSN INT NOT NULL);

4

Domains:

A constant

CREATE DOMAIN SSN_TYPE AS CHAR(9);

5

Primary Key Declartion

CREATE TABLE STUDENT(
Fname VARCHAR(15) NOT NULL,
SSN INT NOT NULL,
PRIMARY KEY(SSN));

6

Unique Key Declaration

Provides alternative keys

CREATE TABLE STUDENT(
Fname VARCHAR(15) NOT NULL,
SSN INT NOT NULL,
SID INT UNIQUE
PRIMARY KEY(SSN));

7

Default declaration

For when a value isn't specified:

Dno INT NOT NULL DEFAULT 3;

8

SQL Insert

INSERT INTO EMP VALUES(
"Bob", "Jones", 9995478414);

For specific attributes:

INSERT INTO EMP(Fname, Lname) VALUES(
"Bob", "Jones");

Anything not set is NULL

9

SQL Delete

DELETE FROM EMP
WHERE Fname = "Bob" AND Lname = "Smith";

Delete entire table

DELETE FROM EMP

10

SQL Update

UPDATE EMP
SET Lname = "Steppers
WHERE Ssn = 4784514784;

Update more than one person

UPDATE EMP
SET Sal = Sal * 1.1
WHERE Dno = 5

11

SQL Join

SELECT Fname, Lname
FROM EMP, DEPT
WHERE Dname = "Research" AND Ename = "John"

12

Attribute Wildcard

Can use * to denote all attributes from a selected entity
SELECT *
FROM EMP

13

SQL UNION, INTERSECT, EXCEPT

(SQL STATEMENT)
UNION
(SQL STATEMENT)

Same goes for the intersection and except

14

Where Wildcard

For zero or more characters

Select Lname
FROM EMP
WHERE Lname LIKE 'sellers.%'

For exactly one character

SELECT Lname
FROM EMP
WHERE Lname LIKE 'sellers.18_'

15

Arithmetic Ops

SELECT 1.1 * SALARY AS INCREASE

16

Ranges

Can use <= or >= or BETWEEN keyword

SELECT *
FROM EMP
WHERE SAL >= 40000 AND SAL <= 60000

SELECT *
FROM EMP
WHERE SAL BETWEEN 40000 AND 60000

17

Order by:

Forces the results to come back in a specific order

SELECT *
FROM EMP
ORDER BY Lname DESC, S_ssn

Would order first by the last name in descending order, if there was a tie, then order by the super's ssn.

18

Views:

A virtual table, changes to a view table reflect back to the actual tables they are derived from

CREATE VIEW V1
AS SELECT Fname, Lname, Dept_name
FROM EMP, DEPT
WHERE Ssn = Essn

19

Compact OR's

SELECT Lname
FROM EMP
WHERE Dno IN (25,47,58)

Gets all the last names where the Dno is 25 or 47 or 58

20

Nested queries

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

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