35.12. Chapter Summary
32.3. SQL
Structured Query Language (SQL) is the language for defining tables and integrity constraints and for accessing and manipulating data. SQL (pronounced "S-Q-L" or "sequel") is the universal language for accessing relational database systems. Application programs may allow users to access a database without directly using SQL, but these applications themselves must use SQL to access the database. This section introduces some basic SQL commands.
Note
| | There are hundreds of relational database management systems. They share the common SQL language but do not all support every feature of SQL. Some systems have their own extensions to SQL. This section introduces standard SQL supported by all systems. |
32.3.1. Creating and Dropping Tables
Tables are the essential objects in a database. To create a table, use the create table statement to specify a table name , attributes, and types, as in the following example:
create table Course ( courseId char ( 5 ), subjectId char ( 4 ) not null , courseNumber integer, title varchar ( 50 ) not null , numOfCredits integer , primary key (courseId) );
This statement creates the Course table with attributes courseId , subjectId , courseNumber , title , and numOfCredits . Each attribute has a data type that specifies the type of data stored in the attribute. char(5) specifies that courseId consists of five characters . varchar(50) specifies that title is a variant-length string with a maximum of fifty characters. integer specifies that courseNumber is an integer. The primary key is courseId .
The tables Student and Enrollment can be created as follows :
create table Student ( ssn char ( 9 ), firstName varchar ( 25 ), mi char ( 1 ), lastName varchar ( 25 ), birthDate date , street varchar ( 25 ), phone char ( 11 ), zipCode char ( 5 ), deptId char ( 4 ), primary key (ssn) ); create table Enrollment ( ssn char( 9 ), courseId char( 5 ), dateRegistered date , grade char( 1 ), primary key (ssn, courseId), foreign key (ssn) references Student, foreign key (courseId) references Course );
Note
| | SQL keywords are not case-sensitive. This book adopts the following naming conventions: Tables are named in the same way as Java classes, and attributes are named in the same way as Java variables . SQL keywords are named in the same way as Java keywords. |
If a table is no longer needed, it can be dropped permanently using the drop table command. For example, the following statement drops the Course table:
drop table Course; If a table to be dropped is referenced by other tables, you have to drop the other tables first. For example, if you have created the tables Course , Student , and Enrollment and want to drop Course , you have to first drop Enrollment , because Course is referenced by Enrollment .
32.3.2. Using SQL on a Relational Database
SQL can be used on MySQL, Oracle, Sybase, IBM DB2, IBM Informix, Borland Interbase, MS Access, or any other relational database system. This chapter uses MySQL to demonstrate SQL and uses MySQL, Access, and Oracle to demonstrate JDBC programming. The Companion Website ( www.prenhall.com/liang/intro6e.html ) contains the following supplements on how to install and use SQL on three popular databases, MySQL, Oracle, and Access:
-
Supplement IV.B: Tutorial for MySQL
-
Supplement IV.C: Tutorial for Oracle
-
Supplement IV.D: Tutorial for Microsoft Access
Assume that you have installed MySQL with the default configuration; you can access MySQL from the DOS command prompt using the command mysql from the c:\mysql\bin directory, as shown in Figure 32.7.
Figure 32.7. You can access a MySQL database server from the command window.
Note
| | On Windows, your MySQL database server starts every time your computer starts. You can stop it by typing the command net stop mysql and restart it by typing the command net start mysql . |
By default, the server contains two databases named mysql and test. You can see these two databases displayed named mysql and test in Figure 32.8(a) using the command show databases .
Figure 32.8. (a) The show databases command displays all available databases in the MySQL database server. (b) The use test command selects the test database.
The mysql database contains the tables that store information about the server and its users. This database is intended for the server administrator to use. For example, the administrator can use it to create users and grant or revoke user privileges. Since you are the owner of the server installed on your system, you have full access to the mysql database. However, you should not create user tables in the mysql database. You can use the test database to store data or create new databases. You can also create a new database using the command create database databasename or drop an existing database using the command drop database databasename .
To select a database for use, type the use databasename command. Since the test database is created by default in every MySQL database, let us use it to demonstrate SQL commands. As shown in Figure 32.8(b), the test database is selected. Enter the statement to create the Course table, as shown in Figure 32.9.
Figure 32.9. The execution result of the SQL statements is displayed in the MySQL monitor.
If you make typing errors, you have to retype the whole command. To avoid retyping the whole command, you can save the command in a file, and then run the command from the file. To do so, create a text file to contain commands, named, for example, test.sql. You can create the text file using any text editor, such as NotePad, as shown in Figure 32.10. To comment a line, precede it with two dashes. You can now run the script file by typing source test.sql from the SQL command prompt, as shown in Figure 32.11.
Figure 32.10. You can use Notepad to create a text file for SQL commands.
Figure 32.11. You can run the SQL commands in a script file from MySQL.
32.3.3. Simple Insert, Update, and Delete
Once a table is created, you can insert data into it. You can also update and delete records. This section introduces simple insert, update, and delete statements.
The general syntax to insert a record into a table is:
insert into tableName [(column1, column2, ..., column]] values (value1, value2, ..., valuen);
For example, the following statement inserts a record into the Course table. The new record has the courseId '11113', subjectId 'CSCI', courseNumber 3720, title 'Database Systems', and creditHours 3.
insert into Course (courseId, subjectId, courseNumber, title, numOfCredits) values ('11113', 'CSCI', '3720', 'Database Systems', 3);
The column names are optional. If the column names are omitted, all the column values for the record must be entered even though the columns have default values. String values are case-sensitive and enclosed inside single quotation marks in SQL.
The general syntax to update a table is:
update tableName set column1 = newValue1 [, column2 = newValue2 , ...] [ where condition];
For example, the following statement changes the numOfCredits for the course whose title is Database Systems to 4:
update Course set numOfCredits = 4 where title = 'Database Systems';
The general syntax to delete records from a table is:
delete [ from ] tableName [ where condition];
For example, the following statement deletes the Database Systems course from the Course table:
delete Course where title = 'Database System';
The following statement deletes all the records from the Course table:
delete Course; 32.3.4. Simple Queries
To retrieve information from tables, use a select statement with the following syntax:
select column-list from table-list [ where condition];
The select clause lists the columns to be selected. The from clause refers to the tables involved in the query. The optional where clause specifies the conditions for the selected rows.
Query 1: Select all the students in the CS department, as shown in Figure 32.12.
Figure 32.12. The result of the select statement is displayed in a window.
(This item is displayed on page 1102 in the print version)
select firstName, mi, lastName from Student where deptId = 'CS';
32.3.5. Comparison and Boolean Operators
SQL has six comparison operators, as shown in Table 32.1, and three Boolean operators, as shown in Table 32.2.
Table 32.1. Comparison Operators
| Operator | Description |
|---|---|
| = | Equal to |
| <> or != | Not equal to |
| < | Less than |
| <= | Less or equal to |
| > | Greater than |
| >= | Greater than |
Table 32.2. Boolean Operators
| Operator | Description |
|---|---|
| not | logical negation |
| and | logical conjunction |
| or | logical disjunction |
Note
| | The comparison and Boolean operators in SQL have the same meaning as in Java. In SQL the equal to operator is = , but it is == in Java. In SQL the not equal to operator is <> or != , but it is != in Java. The not , and , and or operators are ! , && ( & ), and ( ) in Java. |
Query 2: Get the names of the students who are in the CS dept and live in the zip code 31411.
select firstName, mi, lastName from Student where deptId = 'CS' and zipCode = '31411';
Note
| | To select all the attributes from a table, you don't have to list all the attribute names in the select clause. Instead you can just specify an asterisk (*), which stands for all the attributes. For example, the following query displays all the attributes of the students who are in the CS dept and live in the zip code 31411: select * from Student where deptId = 'CS' and zipCode = '31411'; |
32.3.6. The like , between-and , and is null Operators
SQL has a like operator that can be used for pattern matching. The syntax to check whether a string s has a pattern p is
s like p or s not like p
You can use the wild card characters % (percent symbol) and _ (underline symbol) in the pattern p . % matches zero or more characters, and _ matches any single character in s . For example, lastName like '_mi%' matches any string whose second and third letters are m and i . lastName not like '_mi%' excludes any string whose second and third letters are m and i .
Note
| | On the earlier version of MS Access, the wild card character is * , and the character ? matches any single character. |
The between-and operator checks whether a value v is between two other values, v1 and v2 , using the following syntax:
v between v1 and v2 or v not between v1 and v2
v between v1 and v2 is equivalent to v >= v1 and v <= v2 , and v not between v1 and v2 is equivalent to v < v1 and v > v2 .
The is null operator checks whether a value v is null using the following syntax:
v is null or v is not null
Query 3: Get the social security numbers of the students whose grades are between 'C' and 'A.'
select ssn from Enrollment where grade between 'C' and 'A';
32.3.7. Column Alias
When a query result is displayed, SQL uses the column names as column headings. Usually the user gives abbreviated names for the columns, and the columns cannot have spaces when the table is created. Sometimes it is desirable to give more descriptive names in the result heading. You can use the column aliases with the following syntax:
select columnName [ as ] alias
Query 4: Get the last name and zip code of the students in the CS department. Display the column headings as Last Name for lastName and Zip Code for zipCode. The query result is shown in Figure 32.13.
Figure 32.13. You can use a column alias in the display.
select lastName as "Last Name", zipCode as "Zip Code" from Student where deptId = 'CS';
Note
| | The as keyword is optional in MySQL and Oracle but is required in MS Access. |
32.3.8. The Arithmetic Operators
You can use the arithmetic operators * (multiplication), / (division), + (addition), and - (subtraction) in SQL.
Query 5: Assume that a credit hour is fifty minutes of lectures; get the total minutes for each course with the subject CSCI. The query result is shown in Figure 32.14.
Figure 32.14. You can use arithmetic operators in SQL.
select title, 50 * numOfCredits as "Lecture Minutes Per Week" from Course where subjectId = 'CSCI';
32.3.9. Displaying Distinct Tuples
SQL provides the distinct keyword, which can be used to suppress duplicate tuples in the output. For example, the following statement displays all the subject IDs used by the courses:
select subjectId as "Subject ID" from Course;
This statement displays all the subject IDs. To display distinct tuples, add the distinct keyword in the select clause, as follows:
select distinct subjectId as "Subject ID" from Course;
When there is more than one item in the select clause, the distinct keyword applies to all the items that find distinct tuples.
32.3.10. Displaying Sorted Tuples
SQL provides the order by clause to sort the output using the following general syntax:
select column-list from table-list [ where condition] [ order by columns-to-be-sorted];
In the syntax, columns-to-be-sorted specifies a column or a list of columns to be sorted. By default, the order is ascending . To sort in descending order, append the desc keyword. You could also append the asc keyword, but it is not necessary. When multiple columns are specified, the rows are sorted based on the first column, then the rows with the same values on the second column are sorted based on the second column, and so on.
Query 6: List the full names of the students in the CS department, ordered primarily on their last names in descending order and secondarily on their first names in ascending order. The query result is shown in Figure 32.15.
Figure 32.15. You can sort results using the order by clause.
select lastName, firstName, deptId from Student where deptId = 'CS' order by lastName desc, firstName asc;
32.3.11. Joining Tables
Often you need to get information from multiple tables, as demonstrated in the next query.
Query 7: List the courses taken by student Jacob Smith. To solve this query, you need to join tables Student and Enrollment , as shown in Figure 32.16.
Figure 32.16. Student and Enrollment are joined on ssn .
You can write the query in SQL:
select distinct lastName, firstName, courseId from Student, Enrollment where Student.ssn = Enrollment.ssn and lastName = 'Smith' and firstName = 'Jacob';
The tables Student and Enrollment are listed in the from clause. The query examines every pair of rows, each made of one item from Student and another from Enrollment , and selects the pairs that satisfy the condition in the where clause. The rows in Student have the last name, Smith, and the first name, Jacob, and both rows from Student and Enrollment have the same ssn values. For each pair selected, lastName and firstName from Student and courseId from Enrollment are used to produce the result, as shown in Figure 32.17. Student and Enrollment have the same attribute ssn . To distinguish them in a query, use Student.ssn and Enrollment.ssn .
Figure 32.17. Query 7 demonstrates queries involving multiple tables.
EAN: N/A
Pages: 503