SQL Training SQL Statements – Part 1

SQL Training SQL Statements – Part 1

Lesson Objectives At the end of this section you will be able to: • Explain the role of SQL • Write basic SQL Select statements with compound where clauses

Lesson Plan What is SQL Select Statement Where Clause In, Like, Between Workshop

Structured Query Language

Structured Query Language SQL is the language most commonly used to create and process data in relational databases. SQL can be used with Access, DB2, MySQL, Oracle, MS SQL Server, Sybase, or any other relational database.

Select Syntax SELECT customerName, billingCity, count(incidentID) FROM Customer, Incident WHEREcustomerID = reportedByCustomerID and billingCity = 'New York' GROUP BY customername, billingCity HAVING count(incidentID) > 50 ORDER BY customerNamedesc SELECTfield, field, field FROMtable, table, view WHEREcondition and condition or condition GROUP BY field, field, field HAVING … ORDER BY field asc

Select – All Rows Problem: Get a list of all the records and fields in the Fuelsource table. SELECT*FROMFuelsource;

Select – Specifying Columns Problem: Get a list of all the products that use propane (fuelsourceid = 3). select productcode, productdescription, productprice from product where fuelsourceid = 3 21 Rows

Select - Distinct Problem: What are the voltages of the products sold? SELECTDISTINCT voltage FROMProduct

Select - Concatenation of Fields The Concatenation Function can be used to combine multiple fields into a single field. SELECTvendornamename, vendorfirstname|| ' ' ||vendorlastnameas Name FROMvendor ORDERBYvendorname;

Adding Where Clauses

Select - Where Condition The WHERE clause restricts the rows selected to those for which the condition is TRUE. If you omit this clause, Oracle returns all rows from the tables, views, or snapshots in the FROM clause. Examples: WHERE subscribedProductID > 5 WHERE city = 'New York' WHERE hourlyrate BETWEEN 60 and 80 WHERE subscribedProductName LIKE '%Shopper%' WHERE checkout IS NOT NULL WHERE customerID IN (2, 3, 6)

Select – Where Predicate Problem: List of all vendors in California. SELECTvendornameFROM Vendor WHEREprovinceabbreviation= ‘CA';

Select – Compound Where Problem: List of all the products that use Natural Gas, have a power rating of 7000 or 5000 and a frequency of 50.

Select - Null Predicate Problem: List all the RequiredProductrows that do not have a endEffectiveDate. SELECTRequiredProductName, startEffectiveDate, endEffectiveDate FROMRequiredProduct WHEREendEffectiveDateIS NULL ORDER BY RequiredProductName; 161 Rows SELECTRequiredProductName, startEffectiveDate, endEffectiveDate FROMRequiredProduct WHEREendEffectiveDateIS NOT NULL ORDER BY RequiredProductName; 0 Rows

Select – Between Predicate Problem: List products where the product price is between $ 7063 and $ 7300.

Select – Between Predicate – Using Dates SELECTRequiredProductID, RequiredProductName,startEffectiveDate FROMRequiredProduct WHEREstartEffectiveDate BETWEENto_date('03/04/2004','MM/DD/YYYY') and to_date('06/01/2004', 'MM/DD/YYYY') ORDER BY startEffectiveDate; 6 Rows

Select – Like Predicate Problem: Select all rows in the Fuelsourcetable that have a description containing the word ‘Gas’.

Select – Like Predicate Caution: Like Predicate is Case Sensitive. SELECTRequiredProductID, RequiredProductName FROMRequiredProduct WHERELOWER (RequiredProductName)Like '%web%'; 1 Row LOWERSyntax LOWER(char) Returns char, with all letters lowercase. UPPER Syntax UPPER(char) Returns char, with all letters uppercase. Note: This function doesn’t work on some languages (Chinese). Not all SQL version support this function. The classroom server supports this function.

Select – In Predicate Problem: List all Vendors in CT, PA, FL;

Workshop

Individual SQL Workshop 1 • Using Oracle SQL Developer you will create the SQL statements required to produce the requested output. • You will begin by logging onto the training Database. • You have been provided with a list of frequent error messages. This is not a complete list and the resolution may not always one of the options listed as there are numerous causes for many of the errors. But these are the most frequent and will be helpful.

Common Error Messages ORA-00918: column ambiguously defined If a column appears in multiple tables, you must qualify the fieldname with the table name. ORA-00933: SQL command not properly ended Look for missing single quotes around strings OR missing ‘and’ between Where clause statements OR Missing keywords such as Where, From, Group By, Order By ORA-00904: “String Expression": invalid identifier Make sure you are using single quotes and not double quotes. ORA-00904: “CUSTOMER"."CUSTOMERID": invalid identifier Make sure the table you are referencing is in the From clause OR Make sure the field you are referencing is in one of the tables in your From clause OR make sure you have spelled the table and/or field name correctly.

Workshop • Write the SQL to answer the business question. • Only include the columns shown in the picture. • Your answer should match the data shown. • In some cases, only the first and last rows will be shown due to the size constraints of the page. • Note: Oracle does not always print the entire column name – look at the Database Design for column names.

Problem 1 – Simple Select List all products in the Countries in the country table.

Problem 2 – Simple Select with Where Show all components with restockdaycount= 20. 35 Rows

Problem 3 – Select with Between List all components with a weight between 18 and 30 ordered by the weight. 153 Rows

Problem 4 – Select with Compound Where From the Vendor table, list all vendors and their userid when the vendor is in California or the userid is between 60 and 65.

Problem 5 – Select using the IN Predicate From the vendor table, list the information for provinceid= 5 (California) or = 32 (New York) or = 49 (Wisconsin)or = 35 (Ohio) .

Problem 6 – Select using NULL Predicate List all the vendors that do not have a fax.

Problem 7 – Select using Distinct From the Vendor table, list all the cities where there are vendors. Only list each city once. 30 Rows

Problem 8 – Select using LIKE Predicate List all products with ‘9000’ or ‘7000’ in their name and the product name also has ‘110v’ as part of it.

SQL Training SQL Statements – Part 1

SQL Training SQL Statements – Part 1

Presentation Transcript