Week 4

Question 1

2 Major components in SQL?

Answer 1

1. DDL for defining database structure

2. DML for retrieving and updating data

Question 2

Create table?

Answer 2

CREATE TABLE Staff(staffNo VARCHAR(5),
lName VARCHAR(15),
salary DECIMAL(7,2));

Question 3

insert into?

Answer 3

INSERT INTO Staff VALUES (‘SG16’, ‘Brown’, 8300);

Question 4

SQL Commands rules?

Answer 4

1. uppercase letters represent reserved words
2. lowercase letters represent user-defined words
3. | indicates a choice among alternatives
4. curly braces indicate a required element
5. square braces indicate an optional element
6. … indicates optional repetition (0 or more)

Question 5

DISTINCT

Answer 5

SELECT DISTINCT propertyNo

FROM Viewing

Question 6

WHERE

Answer 6

SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > 10000

Question 7

WHERE OR

Answer 7

SELECT *
FROM Branch
WHERE city = ‘London’ OR city = ‘Glasgow’

Question 8

BETWEEN

Answer 8

SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary BETWEEN 20000 AND 30000

Question 9

IN

Answer 9

SELECT staffNo, fName, lName, position
FROM Staff
WHERE position IN (‘Manager’, ‘Supervisor’)

Question 10

Pattern Matching, LIKE, %

Answer 10

SELECT ownerNo, fName, lName, address, telNo
FROM PrivateOwner
WHERE address LIKE ‘%Glasgow%’

Question 11

IS NULL, IS NOT NULL

Answer 11

SELECT clientNo, viewDate
FROM Viewing
WHERE propertyNo = ‘PG4’ AND comment IS NULL

Question 12

ORDER BY DESC

Answer 12

SELECT stafNo, fName, lName, salary
FROM Staff
ORDER BY salary DESC

Question 13

COUNT, MIN, and MAX can apply to non-numeric?

Answer 13

YES

Only SUM and AVG may be used on numeric fields only.

Question 14

what COUNT(*) do?

Answer 14

counts all rows of a table, regardless of whether nulls or duplicate values occur

Question 15

Aggregate functions can be used only in?

Answer 15

SELECT and HAVING clause

Question 16

COUNT(*)

Answer 16

SELECT COUNT(*) AS myCount
FROM PropertyForRent
WHERE rent > 350

Question 17

COUNT(DISTINCT)

Answer 17

SELECT COUNT(DISTINCT propertyNo) AS myCount
FROM Viewing
WHERE viewDate BETWEEN ‘1-May-13’ AND ‘31-May-13’

Question 18

SELECT clause may only contain?

Answer 18

1. column names
2. aggregate functions
3. constants
4. expression involving combinations of the above

Question 19

GROUP BY

Answer 19

SELECT branchNo,
    COUNT(staffNo) AS myCount,
    SUM(salary) AS mySUM
FROM Staff
GROUP BY branchNo
ORDER BY branchNo

Question 20

HAVING

Answer 20

SELECT branchNo,
    COUNT(staffNo) AS myCount,
    SUM(salary) AS mySUM
FROM Staff
GROUP BY branchNo
    HAVING COUNT(staffNo) > 1
ORDER BY branchNo

Question 21

Perbedaan ANY dan ALL

Answer 21

ANY and ALL may be used with subqueries that produce a single column of numbers

ALL, condition will only be true if it is satisfied by ALL values produced by subquery

ANY, condition will be true if it is satisfied by ANY values produced by subquery

if subquery is empty, ALL returns true, ANY returns false

Question 22

SIMPLE JOIN

Answer 22

SELECT c.clientNo, fName, lName, propertyNo, comment
FROM Client c, Viewing v
WHERE c.clientNo = v.clientNo

Question 23

THREE TABLE JOIN

Answer 23

SELEC b.branchNo, b.city, s.staffNo, fName, lName
FROM Branch b, Staff s, PropertyForRent p
WHERE b.branchNo = s.branchNo AND s.staffNo = p.staffNo
ORDER BY b.branchNo, s.staffNo, propertyNo

Question 24

Left Outer Join

Answer 24

SELECT b., p.

FROM Branch1 b LEFT JOIN PropertyForRent1 ON b.bCity = p.pCity

Question 25

Right Outer Join

Answer 25

SELECT b., p.

FROM Branch1 b RIGHT JOIN PropertyForRent1 ON b.bCity = p.pCity

Question 26

Full Outer Join

Answer 26

SELECT b., p.

FROM Branch1 b FULL JOIN PropertyForRent1 ON b.bCity = p.pCity

Question 27

EXISTS

Answer 27

SELECT staffNo, fName, lName, position
FROM Staff s
WHERE EXISTS (
    SELECT *
    FROM Branch b
    WHERE s.branchNo = b.branchNo AND city = 'London')

Question 28

UNION

Answer 28

(SELECT city
FROM Branch
WHERE city IS NOT NULL) 
UNION
(SELECT city
FROM PropertyForRent
WHERE city IS NOT NULL)

Question 29

INTERSECT

Answer 29

(SELECT city FROM BRANCH)
INTERSECT
(SELECT city FROM PropertyForRent)

Question 30

EXCEPT

Answer 30

(SELECT city FROM Branch)
EXCEPT
(SELECT city FROM PropertyForRent)

Question 31

INSERT INTO

Answer 31

INSERT INTO Staff (staffNo, fName, lName, position, salary, branchNo) VALUES (‘SG44’, ‘Anne’, ‘Jones’, Assistant’, 8100, ‘B003’)

Question 32

UPDATE

Answer 32

UPDATE Staff

SET salary = salary*1.03

Question 33

UPDATE Multiple Columns

Answer 33

UPDATE Staff
SET position = ‘Manager’, salary = 18000
WHERE staffNo = ‘SG14’

Question 34

DELETE FROM

Answer 34

DELETE FROM Viewing

WHERE propertyNo = ‘PG4’

Question 35

Delete all records from Viewing Table

Answer 35

DELETE FROM Viewing