Week 1 - 4 Flashcards
What SQL Statement lists the table names of all the tables that you have created?
SELECT table_name
FROM user_tables;
What SQL statement lists the column details of a table that you have created?
DESCRIBE ;
What SQL Statement deletes a table?
DROP TABLE ;
Create a table called subject with typical attributes and a foreign key from a table named Lecturer.
Create Table SUBJECT ( SubjectCode Varchar2(10) , Title Varchar2(100) , CreditPoints Number, LecId Number, Primary Key (SubjectCode), Foreign Key (LecId) References Lecturer );
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.
a) One
b) Many
What does the following error indicate?
ORA-02292: integrity constraint (s1234567.SYS_C002969703) violated - child record found
A parent (primary key) value has attempted to be deleted without first deleting the child (foreign key) value.
What does the following error indicate?
ORA-02291: integrity constraint (s1234567.SYS_C002969703) violated - parent key not found
No parent (primary key) value matched the value attempted to be used in the constraint.
Write an SQL statement that inserts values into a SUBJECT table but the lecturer is unknown.
INSERT INTO SUBJECT ( SubjectCode, Title, CreditPoints, LecId )
VALUES (‘INF21009’, ‘Advanced Facebook’, 12.5, Null);
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 );
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 );
What does the following error indicate?
ORA-01400: cannot insert NULL into (s1234567.SUBJECT.LECID)
An attempt has been made to insert a row that breaks the Not Null constraint.
A single line indicates that participation is a)_____.
A circle indicates that participation is b)_____.
a) MUST
b) MAY
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.
SELECT Test1, Test2, Test1+Test2 AS “TestSum”
FROM StudentResults;
Write an SQL statement that shows an alternate column name to a column named Name from the table StudentResults.
SELECT Name “StudentName”
FROM StudentResults;
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’;
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';
When a WHERE clause contains both an AND & OR operator then the ___ operators take precedence and must be evaluated first.
AND
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
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’);
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
Yes.
What is the order of precedence for the following?
Brackets, NOT, Relational Operators (, = etc.), OR, AND.
- Relational Operators (, = etc.)
- Brackets
- NOT
- AND
- OR
What is the syntax to return a value where a) all characters are in upper case, and b) all characters are in lower case?
a)
UPPER ()
b)
LOWER ()
Select all columns from a table Employee with multiple Smiths in the sname column. Note that some of them have all uppercase letters.
SELECT *
FROM Employee
Where UPPER(sname) = ‘SMITH’;
What is an alternative way to write:
WHERE age >= 30 AND age <= 40
WHERE age BETWEEN 30 and 40
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
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%’
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
a)
WHERE upper(name) IN ('JONES', 'BROWN', 'LEE', 'JOHNSON')
b)
WHERE age IN (24, 26, 28, 30)
Write a WHERE clause that searches for heights that have null values and names that don’t have null values
WHERE height IS NULL OR name IS NOT NULL
Write an SQL statement that will not select multiple rows with the same value in the column Country from a table called Student
SELECT DISTINCT Country FROM Student;
or
SELECT UNIQUE Country FROM Student;
