1 / 12

Master SQL: Learn Structured Query Language

SQL, an ANSI standard language, accesses databases for querying, data manipulation, and table creation. Understand SQL SELECT, INSERT, UPDATE, DELETE statements, WHERE clause, COUNT, and more. Enhance your SQL skills!

meidinger
Download Presentation

Master SQL: Learn Structured Query Language

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CIS 375—Web App Dev II SQL

  2. Introduction • SQL (Structured _______ Language) is an ANSI standard language for accessing databases. • SQL can execute queries, retrieve data, insert new records, delete records, and update records in a database. • SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc. • A database contains one or more ______. Each table is identified by a name and contains records (rows) and fields (columns). • Besides W3Schools, you can also try http://www.sqlcourse.com/ (optional).

  3. SQL Queries, DML, DDL • Suppose a table named Persons has fields SSN, LastName, FirstName, Age, Address, City and has ten records. • An SQL query: • SELECT LastName FROM Persons • Provides a list of all ten last names in the table • SQL also has a DML (Data ___________ Language) to update, insert, and delete records. • SQL also has a DDL (Data __________ Language) to do things like create and delete tables, and to create table indexes (________ keys). • For example, SSN would be an index in Persons.

  4. SQL: The SELECT Statement • The SELECT statement specifies ________ of data to be returned from a table. • Examples of SQL SELECT statements for the table Persons. • SELECT LastName, FirstName FROM Persons (use a “,” to separate column names) • SELECT * FROM Persons (* means “all”) • Executing an SQL statement produces a ________ set of data. • Some database programs require a “;” after each SQL statement, but many don’t.

  5. SQL: SELECT DISTINCT • A SELECT statement could possibly result in multiple values. • Use DISTINCT to avoid this possibility. • Example: • Suppose you want a list of all distinct cities in the Persons table. • SELECT DISTINCT City FROM Persons • Without DISTINCT, the city of Springfield, for example, might appear several times in the list (for each person who lives in Springfield).

  6. SQL: The WHERE Clause • The WHERE clause specifies _____ of data to be returned from a table. • Example of SQL WHERE clause. • SELECT * FROM Persons WHERE City=‘Springfield’ (returns all fields for records where City is Springfield) • Use quotes around text, but not around numbers. • Other operators include <>, >, <, >=, <=, __________, and LIKE. • SELECT LastName, FirstName FROM Persons WHERE City BETWEEN ‘A’ AND ‘M’ • SELECT * FROM Persons WHERE LastName LIKE ‘A%’ (returns all last names beginning with A)

  7. SQL: INSERT INTO Statement • To insert a new row into a table: • INSERT INTO Persons  VALUES (‘Bush', ‘George W.', 56, ‘1600 Pennsylvania Ave.', ‘Washington, D.C.') • To insert only certain values in certain columns of a new row: • INSERT INTO Persons (LastName, Address) VALUES (‘Hood', ‘Sherwood Forest')

  8. SQL: UPDATE Statement • To modify data in an existing row: • UPDATE Persons SET FirstName = ‘Rick' WHERE LastName = ‘Johnson‘ • Update several columns in a row: • UPDATE Persons SET Address = 'Stien 12', City = 'Stavanger' WHERE LastName = 'Rasmussen'

  9. SQL: DELETE Statement • To delete one or more rows from a table: • DELETE FROM Persons WHERE LastName = 'Rasmussen' • To delete all rows: • DELETE * FROM Persons

  10. SQL: ORDER BY • ORDER BY is used for _________ results of a query. • Example: • SELECT LastName, FirstName FROM Persons ORDER BY LastName • Sort by multiple columns: • SELECT * FROM Persons ORDER BY LastName, FirstName • Sort in descending or ascending order: • SELECT * FROM Persons ORDER BY City DESC, LastName ASC • You can test your SQL skills with a real database at http://www.w3schools.com/sql/sql_tryit.asp.

  11. SQL: AND, OR, NOT • AND and OR are used to join conditions in a _______ clause. • Examples: • SELECT * FROM Persons WHERE LastName = ‘Johnson’ AND FirstName = ‘Richard’ • SELECT * FROM Persons WHERE (FirstName='Tove' OR FirstName='Stephen') AND LastName='Svendson' • You can also use the keyword _____ with many different kinds of SQL statements: • SELECT * FROM Persons WHERE NOT (City=‘Berlin' OR City=‘London') • SELECT LastName, FirstName FROM Persons WHERE City NOT BETWEEN ‘A’ AND ‘M’

  12. SQL: COUNT Statement • To count all rows in a table (‘*’ means no ________ specified): • SELECT COUNT(*) FROM Persons • Using COUNT with a WHERE clause: • SELECT COUNT(*) FROM Persons WHERE Age>20 • When a column is specified, the null entries are not counted: • SELECT COUNT(Age) FROM Persons • To count distinct entries in a particular column: • SELECT COUNT(DISTINCT City) FROM Persons

More Related