Database Processing Eighth Edition Structured Query Language Chapter

Database Processing Eighth Edition Structured Query Language Chapter 9 David M. Kroenke 1 © 2002 by Prentice Hall

Structure Query Language • Structure Query Language is known as either – Its acronym, SQL, or – SEQUEL, the name of the original version of SQL • SEQUEL was developed by IBM in the mid-1970 s. 2 © 2002 by Prentice Hall

SQL, not a Procedural Programming Language • SQL is not a programming language itself, it is a data access language • SQL may be embedded in traditional procedural programming languages (like COBOL) 3 © 2002 by Prentice Hall

SQL Syntax SQL is not case sensitive. SELECT field(s) ‘what columns will be retrieved FROM table(s); ‘which table contains the column data e. g. , SELECT Name, Phone FROM Student; 4 © 2002 by Prentice Hall

The DISTINCT Qualifier • Eliminating duplicate rows on the output… SELECT DISTINCT State. Address FROM Employee; 5 © 2002 by Prentice Hall

The WHERE Clause • Reducing the output based on specified criteria… SELECT Student. Name FROM Students WHERE Grade. Point. Average >= 3. 0; 6 © 2002 by Prentice Hall

Comparison Operators Equals = Not equals <> Greater than > Less than < Greater than or equal to >= Less than or equal to <= Within a list of values IN A logical NOT Within a range BETWEEN 7 © 2002 by Prentice Hall

IN a List of Values SELECT Student. Name FROM Student WHERE State IN [‘PA’, ‘MA’, ‘CA’]; 8 © 2002 by Prentice Hall

The Logical NOT SELECT Student. Name FROM Students WHERE State NOT IN [‘NJ’, ‘NM’, ‘NY’]; SELECT Student. Name FROM Students WHERE NOT Grade. Point. Average >= 3. 0; 9 © 2002 by Prentice Hall

Within a Range of Values SELECT Student. Name FROM Student WHERE Student. ID BETWEEN 250 and 300; 10 © 2002 by Prentice Hall

Using Wildcard Character Substitutions • The LIKE keyword is used in place of the = sign when you use wildcard characters. • The underscore character (_) is a single character substitution • The percent character (%) is a multicharacter substitution 11 © 2002 by Prentice Hall

Using LIKE SELECT Student. ID FROM Student WHERE Student. Name LIKE ‘K%’; SELECT Part. Name FROM Part WHERE Part. Number LIKE ‘_ABC%’; 12 © 2002 by Prentice Hall

NULL Means Nothing • A NULL character means that nothing has been entered. This is different from a space or a zero. SELECT Name FROM Student WHERE Major IS NULL; 13 © 2002 by Prentice Hall

ORDER BY… Sorting Outputs • Sorting in descending order… SELECT Student. ID, Name FROM Student ORDER BY Name DESC; • Sorting in ascending order… SELECT Student. ID, Name FROM Student ORDER BY Name ASC; 14 © 2002 by Prentice Hall

Built-in Functions • • • Counting number of rows COUNT Adding the values in a column SUM Averaging the values in a column AVG Finding the maximum value in a column MAX Finding the minimum value in a column MIN 15 © 2002 by Prentice Hall

Built-in Functions SELECT Count (*) FROM Student WHERE State = ‘WI’; SELECT Sum (Amount) FROM Sales. Receipt; SELECT Max (Score) FROM Assignments; 16 © 2002 by Prentice Hall

Grouping the Output SELECT Name, State FROM Student GROUP BY State; 17 © 2002 by Prentice Hall

Reducing the Groups Displayed SELECT Name, State FROM Student GROUP BY State HAVING Count (*) > 4; 18 © 2002 by Prentice Hall

Sub-Queries SELECT Name FROM Student WHERE SID IN (SELECT Student. Number FROM Enrollment WHERE Class. Name = ‘MIS 445’); 19 © 2002 by Prentice Hall

Joining Tables SELECT Student. SID, Student. Name, Enrollment. Class. Name FROM Student, Enrollment WHERE Student. SID = Enrollment. Student. Number AND Student. State = ‘OH’; 20 © 2002 by Prentice Hall

EXISTS SELECT DISTINCT Student. Number FROM Enrollment A WHERE EXISTS (SELECT * FROM Enrollment B WHERE A. Student. Number = B. Student. Number AND A. Class. Name NOT = B. Class. Name); 21 © 2002 by Prentice Hall

Inputting Data INSERT INTO Enrollment VALUES (400, ‘MIS 445’, 44); 22 © 2002 by Prentice Hall

Deleting Data DELETE Student WHERE Student. SID = 100; 23 © 2002 by Prentice Hall

Modifying Data UPDATE Enrollment SET Seat. Number = 44 WHERE SID = 400; 24 © 2002 by Prentice Hall

Database Processing Eighth Edition Structured Query Language Chapter 9 David M. Kroenke 25 © 2002 by Prentice Hall