SQL Expressions Continued IV

Consider the following table schemas and then go over SQL queries and their corresponding expressions in response to the queries that follow:

Student:

matNr | sName

Professor:

pname | psalary

Class:

classNr | room | day | pname

                                     fk

Takes:

matNr | classNr | grade

TA:

matNr | classNr | hours | tasalary

 

SQL Expressions using Sorting

Q List names, grades of students with the grades in descending order.

SELECT sName, grade

FROM Student, Takes

WHERE Student.matNr=Takes.matNr

ORDER BY grade DESC

Using ORDER BY in the above expression, the grades will be listed in descending order. The default is ASC (ascending order).

Searching for partial strings

The operators LIKE and NOT LIKE use wild card characters.

·         % stands for an arbitrary number (0 or more) of arbitrary characters.

·         _  stands for exactly one arbitrary character

Q1 Find all rooms that were used for classes in CSE.

SELECT room FROM Class WHERE classNr LIKE ‘CSE%’

Q2 Find all students whose names do not end with the letter m.

SELECT sName FROM Student WHERE sName NOT LIKE ‘%m’

Q3 Find all students whose names contain the letter e.

SELECT sName FROM Student WHERE sName LIKE ‘%e%’

SQL Expressions concerning Input of Data

Inserting tuples: (Examples)

INSERT INTO Student VALUES (10, ‘James’)

INSERT INTO Class (pname, classNr) VALUES (‘Jane’, ‘CSE 303’), (‘David’, ‘CSE 304’)

Values that are not specified are automatically either set to NULL or to a default value.

SQL Expressions concerning deletion of tuples

Examples:

DELETE FROM Professor WHERE pname= ‘David’

DELETE FROM Student

·         Deletes all tuples from the table Student

Update of tuples/values

Examples:

Update Class

SET room=’7B03’, day= ‘Friday’

WHERE classNr=’CSE 304’

Several rows can be updated in one statement.

Q. All TAs get a rise of 10% increase of their salary

UPDATE TA

SET tasalary = 1.1 * tasalary

Deletion of tables

DROP TABLE tablename {CASCADE|DELETE}

If CASCADE is specified:

All views and references from other tables that refer to this table are also deleted

If RESTRICT is specified:

Deletion is only executed if the table is not referenced by other tables or views.