Week 1 - 4

Question 1

What SQL Statement lists the table names of all the tables that you have created?

Answer 1

SELECT table_name

FROM user_tables;

Question 2

What SQL statement lists the column details of a table that you have created?

Answer 2

DESCRIBE ;

Question 3

What SQL Statement deletes a table?

Answer 3

DROP TABLE ;

Question 4

Create a table called subject with typical attributes and a foreign key from a table named Lecturer.

Answer 4

Create Table SUBJECT (
SubjectCode Varchar2(10) ,
Title Varchar2(100) ,
CreditPoints Number,
LecId Number,
Primary Key (SubjectCode),
Foreign Key (LecId) References Lecturer
);

Question 5

A parent key is found at the a)______ end of an M:1 relationship and the child is found at the b)______ end of an M:1 relationship.

Answer 5

a) One

b) Many

Question 6

What does the following error indicate?

ORA-02292: integrity constraint (s1234567.SYS_C002969703) violated - child record found

Answer 6

A parent (primary key) value has attempted to be deleted without first deleting the child (foreign key) value.

Question 7

What does the following error indicate?

ORA-02291: integrity constraint (s1234567.SYS_C002969703) violated - parent key not found

Answer 7

No parent (primary key) value matched the value attempted to be used in the constraint.

Question 8

Write an SQL statement that inserts values into a SUBJECT table but the lecturer is unknown.

Answer 8

INSERT INTO SUBJECT ( SubjectCode, Title, CreditPoints, LecId )
VALUES (‘INF21009’, ‘Advanced Facebook’, 12.5, Null);

Question 9

How would you edit the following SQL statement if every subject MUST have a convener and every subject NEEDS a title?

Create Table SUBJECT (
SubjectCode Varchar2(10) ,
Title Varchar2(100) ,
CreditPoints Number,
LecId Number,
Primary Key (SubjectCode),
Foreign Key (LecId) References Lecturer
);

Answer 9

Create Table SUBJECT (
SubjectCode Varchar2(10) ,
Title Varchar2(100) Not Null,
CreditPoints Number,
LecId Number Not Null,
Primary Key (SubjectCode),
Foreign Key (LecId) References Lecturer
);

Question 10

What does the following error indicate?

ORA-01400: cannot insert NULL into (s1234567.SUBJECT.LECID)

Answer 10

An attempt has been made to insert a row that breaks the Not Null constraint.

Question 11

A single line indicates that participation is a)_____.

A circle indicates that participation is b)_____.

Answer 11

a) MUST

b) MAY

Question 12

Write an SQL statement that shows the columns Test1 and Test2 individually and then sum them together in a column named TestSum. Make this from a table called StudentResults.

Answer 12

SELECT Test1, Test2, Test1+Test2 AS “TestSum”

FROM StudentResults;

Question 13

Write an SQL statement that shows an alternate column name to a column named Name from the table StudentResults.

Answer 13

SELECT Name “StudentName”

FROM StudentResults;

Question 14

Write the following SQL statement using alias’s.

SELECT lecturer.LecId, subject.SubjName, lecturer.Age
FROM lecturer
INNER JOIN subject
ON lecturer.LecId = subject.LecId
WHERE lecturer.age => 50 AND subject.SubjName = ‘Database’;

Answer 14

SELECT L.LecId, S.SubjName, L.Age
FROM lecturer L
INNER JOIN subject S
ON L.LecId = S.LecId
WHERE L.Age => 50 AND S.SubjName = 'Database';

Question 15

When a WHERE clause contains both an AND & OR operator then the ___ operators take precedence and must be evaluated first.

Answer 15

AND

Question 16

SELECT * FROM Emp WHERE branch = ‘City’;

If the above SQL statement selects all people who work in the City branch, write a statement that:

a) selects all people from all other brances
b) select all people who work in either City or Haw
c) select all people from all branches except City or Haw

Answer 16

a)
SELECT * FROM Emp WHERE NOT (branch = ‘City’);

b)
SELECT * FROM Emp WHERE branch = ‘City’ OR branch=’Haw’;

c)
SELECT * FROM Emp WHERE NOT (branch=’City’ OR branch=’Haw’);

Question 17

Does the following SQL statements return different results?

SELECT * FROM emp WHERE NOT ( Dept = 1 OR Dept = 2);

SELECT * FROM emp WHERE NOT Dept = 1 OR Dept = 2

Answer 17

Yes.

Question 18

What is the order of precedence for the following?

Brackets, NOT, Relational Operators (, = etc.), OR, AND.

Answer 18

1. Relational Operators (, = etc.)
2. Brackets
3. NOT
4. AND
5. OR

Question 19

What is the syntax to return a value where a) all characters are in upper case, and b) all characters are in lower case?

Answer 19

a)
UPPER ()

b)
LOWER ()

Question 20

Select all columns from a table Employee with multiple Smiths in the sname column. Note that some of them have all uppercase letters.

Answer 20

SELECT *
FROM Employee
Where UPPER(sname) = ‘SMITH’;

Question 21

What is an alternative way to write:

WHERE age >= 30 AND age <= 40

Answer 21

WHERE age BETWEEN 30 and 40

Question 22

Write a WHERE clause that partially matches a text value like the following:

a) starting with J
b) ending in J
c) That has a J anywhere in it
d) That has JO in it next to each other
e) That has JON in it next to each other
f) That has J and an O anywhere in it but in that order

Answer 22

a) WHERE UPPER(name) LIKE ‘J%’
b) WHERE UPPER(name) LIKE ‘%J’
c) WHERE UPPER(name) LIKE ‘%J%’
d) WHERE UPPER(name) LIKE ‘%JO%’
e) WHERE UPPER(name) LIKE ‘%JON%’
f) WHERE UPPER(name) LIKE ‘%J%O%’

Question 23

What is an alternative way to write…
a)
WHERE upper(name) = ‘JONES’ OR upper(name) = ‘BROWN’ OR upper(name) = ‘LEE’ OR upper(name) = ‘JOHNSON’

b)
WHERE age = 24 OR age = 26 OR age = 28 OR age = 30

Answer 23

a)
WHERE upper(name) IN ('JONES', 'BROWN', 'LEE', 'JOHNSON')

b)
WHERE age IN (24, 26, 28, 30)

Question 24

Write a WHERE clause that searches for heights that have null values and names that don’t have null values

Answer 24

WHERE height IS NULL OR name IS NOT NULL

Question 25

Write an SQL statement that will not select multiple rows with the same value in the column Country from a table called Student

Answer 25

SELECT DISTINCT Country FROM Student;
or
SELECT UNIQUE Country FROM Student;