SQL DML Operations
SQL DML Operations I) The SELECT Command This is the most frequently used SQL statement and is used to return a set of records based on selection criteria. The following is the generally format of the SELECT statement: SELECT column_list FROM table_list [WHERE conditional_expression] [GROUP BY group_by_column_lis] [HAVING conditional_expression] [ORDER BY order_by_column_list] All the lists in the statement are comma separated. The following are various SELECT operations a) The Basic SELECT Query This is the simplest SELECT command returns all the records and all the fields from a database table: Syntax SELECT * FROM Table Example: Write an SQL statement that returns all publishers SELECT * FROM Publishers To specify the list of fields you to be retrieved (often to speed up a query by retrieving only the fields you’re actually going to use in the application).
Then the following syntax is used: SELECT ColumnList FROM Table Example: Write an SQL statement that returns the publishers ID, company name and address of all publishers SELECT PubID, [Company Name], Address FROM Publishers Note: you can use uppercase characters or lowercase characters for SQL keywords alike. b) Using Computed Columns (Expressions) SQL supports simple expressions in the field list portion of a SELECT. Syntax: SELECT AS FROM TABLE BIT 2206 Application Programming II ~Wainaina Page 3 of 6 Example, Write an SQL statement to return the name and age of all authors. SELECT Author, 2000-[Year Born] AS Age FROM Authors c) SQL Functions Aggregate functions such as COUNT, MIN, MAX, SUM, and AVG can be used in the table field of a SELECT statement. The following is descriptions of these functions: Function Description COUNT(* | ) Count number of rows in the return results MIN() Minimum value in the specified column MAX() Maximum value in the specified column SUM() Sum of all the values in the specified column AVG() Average of all the value in the specified column Example, Write an SQL statement that returns the number and average age of all authors. SELECT COUNT (*) AS AuthorCnt, AVG (2008-[Year Born]) AS AvgAge FROM Authors Page | 3 d) WHERE CLAUSE This is used to filter a subset of the records in the table i.e. it restricts what rows are considered in the final results. Syntax: . . .
The following is a list of valid SQL operators Operator Description = Equal to > Greater than >= Greater than or equal to < Less than <= Less than or equal to <> Not equal to BETWEEN .. AND Between the given two values IS NULL Values that are null IN Values in the given list LIKE Wildcard matching of values. Allowed wildcards are underscore (_) to be matched with exactly one character and percentage sign (%) to match any number of characters Boolean operators such as AND and OR can be used to combine multiple conditions. Expressions can be negated by the Boolean operator NOT. Example: i) Write an SQL statement that returns the names of all the publishers in California. BIT 2206 Application Programming II ~Wainaina Page 4 of 6 SELECT Name, City FROM Publishers WHERE State = ‘CA’ ii) Write an SQL statement that returns the Californian publishers whose names begin with the letter M: SELECT * FROM Publishers WHERE State = ‘CA’ AND Name LIKE ‘M%’ iii) Write an SQL statement that returns titles published between 1990 and 2000 SELECT * FROM Titles WHERE [Year Published] BETWEEN 1990 AND 2000 iv) Write an SQL statement that returns all the publishers located in California, Texas, and New Jersey. SELECT Name, State FROM Publishers WHERE State IN (‘CA’, ‘TX’, ‘NJ’) Note: The word DISTINCT is used to filter out any repeated rows in the returned results and therefore all there returned rows would be unique.