SQL

Question 1

SQL Definition

Answer 1

High Level, declarative programming language

Specifies what the user wants, not how

Question 2

Terms:

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

Answer 2

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

Question 3

Table Creation in SQL:

Answer 3

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

Question 4

Domains:

Answer 4

A constant

CREATE DOMAIN SSN_TYPE AS CHAR(9);

Question 5

Primary Key Declartion

Answer 5

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

Question 6

Unique Key Declaration

Answer 6

Provides alternative keys

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

Question 7

Default declaration

Answer 7

For when a value isn’t specified:

Dno INT NOT NULL DEFAULT 3;

Question 8

SQL Insert

Answer 8

INSERT INTO EMP VALUES(
“Bob”, “Jones”, 9995478414);

For specific attributes:

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

Anything not set is NULL

Question 9

SQL Delete

Answer 9

DELETE FROM EMP
WHERE Fname = “Bob” AND Lname = “Smith”;

Delete entire table

DELETE FROM EMP

Question 10

SQL Update

Answer 10

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

Update more than one person

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

Question 11

SQL Join

Answer 11

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

Question 12

Attribute Wildcard

Answer 12

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

Question 13

SQL UNION, INTERSECT, EXCEPT

Answer 13

(SQL STATEMENT)
UNION
(SQL STATEMENT)

Same goes for the intersection and except

Question 14

Where Wildcard

Answer 14

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_’

Question 15

Arithmetic Ops

Answer 15

SELECT 1.1 * SALARY AS INCREASE

Question 16

Ranges

Answer 16

Can use <= or >= or BETWEEN keyword

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

SELECT *
FROM EMP
WHERE SAL BETWEEN 40000 AND 60000

Question 17

Order by:

Answer 17

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.

Question 18

Views:

Answer 18

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

Question 19

Compact OR’s

Answer 19

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

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

Question 20

Nested queries

Answer 20

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