The ORDER BY clause is used to force the query result to be sorted based on one or more column values. You can select either ascending or descending sort for each named column. Example 1 List the names and IDs of all faculty members arranged in alphabetical order. SELECT FACID, FACNAME FROM FACULTY ORDER BY…
COLUMN FUNCTIONS (AGGREGATE FUNCTIONS)
Aggregate functions allow you to calculate values based upon all data in an attribute of a table. The SQL aggregate functions are: Max, Min, Avg, Sum, Count, StdDev, Variance. Note that AVG and SUM work only with numeric values and both exclude NULL values from the calculations. Example 1 How many students are there? SELECT…
MORE COMPLEX SINGLE TABLE RETRIEVAL
The WHERE clause can be enhanced to be more selective. Operators that can appear in WHERE conditions include: =, <> ,< ,> ,>= ,<= IN BETWEEN…AND… LIKE IS NULL AND, OR, NOT Example 1 Find the student ID of all math majors with more than 30 credit hours. SELECT STUID FROM STUDENT WHERE MAJOR =…
SIMPLE SINGLE TABLE RETRIEVAL
Example 1 Retrieve all information about students (‘*’ means all attributes) SELECT * FROM STUDENT; STUID LNAME FNAME MAJOR CREDITS S1001 Smith Tom History 90 S1010 Burns Edward Art 63 S1015 Jones Mary Math 42 S1002 Chin Ann Math 36 S1020 Rivera Jane CIS 15 S1013 McCarchy Owen Math 9 Example 2 Find the last…
SQL DATA MANIPULATION LANGUAGE (DML)
The DML component of SQL is the part that is used to query and update the tables (once they are built via DDL commands or other means). By far, the most commonly used DML statement is the SELECT. It combines a range of functionality into one complex command. Used primarily to retrieve data from the…
SQL DATA DEFINITION (DDL)
TABLES CREATE TABLE Define the structure of a new table Format: CREATE TABLE tablename ({col–name type [(size)][constraint],…}); The ‘constraint’ clause in the CREATE TABLE statement is used to enforce referential integrity. Specifically, PRIMARY KEY, FOREIGN KEY, and CHECK integrity can be set when you define the table. The syntax for key and check constraints is…
Structured Query Language
Many database management systems support some version of structured query language (SQL). In some DBMSs (i.e., ORACLE) SQL is the primary data manipulation interface. Consequently, SQL is a very important topic. The purpose of this document is to introduce you to the major SQL statements and to show you how they work. This document will…