1 / 21

SQL Review

SQL Review. Tonga Institute of Higher Education. SQL Introduction. SQL (Structured Query Language) a language that allows a developer to work with data in a database.

medea
Download Presentation

SQL Review

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. SQL Review Tonga Institute of Higher Education

  2. SQL Introduction • SQL (Structured Query Language) a language that allows a developer to work with data in a database. • This presentation serves to provide a basic review of SQL statements. Advanced concepts are covered in IT244 – Database Management Systems • All examples will use the Northwind Access database. This database is available from www.microsoft.com.

  3. Working with Data in a Database • SQL answers 4 common questions: • How do I get data from a database? SELECT • How do I add new data to a database? INSERT • How do I change data in a database? UPDATE • How do I delete data from a database? DELETE

  4. SELECT Statement Basics • How do I get data from a database? Use SELECT! SELECT <Column Names> FROM <Table Name> WHERE <Criteria> • Returns data from the columns of the rows that match the criteria.

  5. SELECT Column Names • Specify the column names that you wish to receive data from. • Multiple column names should be separated by commas. • SELECT CompanyName, ContactName, Phone FROM Customers • An asterick (*) can be used to get data from all the columns. • SELECT * From Customers

  6. SELECT Criteria • Criteria can be added to get specific rows of data. • For example, this code returns rows that include CompanyNames from Customers that have a contact name Maria. SELECT CompanyName FROM Customers WHERE ContactName = ‘Maria’ • In this case, nothing is returned because there is no customer with the Contact Name of exactly ‘Maria’

  7. SELECT Criteria Wildcards • Criteria wildcards can be used to find rows that contains certain pieces of data. • For example, this code returns all CompanyNames from Customers that INCLUDE a contact name Maria. SELECT CompanyName FROM Customers WHERE ContactName like ‘%Maria%’ • Returns 2 rows both include Maria in the ContactName column. Often % is used for wildcards Sometimes * is used for wildcards Any characters can be before Maria Any characters can be after Maria

  8. SELECT Summary • Using the SELECT statement, we can query our database. • For example, to get all the column data from all rows with a contact name that includes Maria, use this code: SELECT * FROM Customers WHERE ContactName like ‘%Maria%’

  9. Comprehension Check SELECT Statement

  10. INSERT Statement Basics • How do I add new data to a database? Use INSERT! • INSERT INTO <Table Name> (<Column Names>) VALUES (<Data>) • Inserts a new row with data in the columns specified.

  11. INSERT Data INSERT INTO Customers (CustomerID, CompanyName) VALUES (‘TICO’, ‘Ti Company and Associates’) • Each column name to the left of VALUES matches a piece of data to the right. • Use commas to separate each column name and piece of data. • String require single quotes. Sometimes, double quotes may be used. • Make sure you insert data for required columns.

  12. INSERT Summary • Using the INSERT statement, we can insert new rows into our database. • For example, to add a new customer with the ID TICO and name of Ti Company and Associates, use this code: INSERT INTO Customers (CustomerID, CompanyName) VALUES (‘TICO’, ‘Ti Company and Associates’)

  13. Comprehension Check INSERT Statement

  14. UPDATE Statement Basics • How do I change data in a database? Use UPDATE! • UPDATE <Table Name> SET <Column Name> = ‘<New Value>’ WHERE <Criteria> • Updates rows that match the criteria by changing the data in the columns specified to the new value.

  15. UPDATE Details UPDATE Customers SET ContactName = ‘Sione Tukuia’, Phone = ‘13533’ WHERE CustomerID = ‘ANATR’ • Use commas to separate each data update. • String require single quotes. Sometimes, double quotes may be used. • Make sure the criteria is correct. If no criteria is included, then all data will be updated.

  16. UPDATE Summary • Using the UPDATE statement, we can update data in our database. • For example, to change the contact name and phone number of the customer with ID ANATR, use the following code: UPDATE Customers SET ContactName = ‘Sione Tukuia’, Phone = ‘13533’ WHERE CustomerID = ‘ANATR’

  17. Comprehension Check UPDATE Statement

  18. DELETE Statement Basics • How do I delete data from a database? Use DELETE! DELETE FROM <Table Name> WHERE <Criteria> • Deletes rows that match the criteria. DELETE FROM Customers WHERE ContactName like ‘%Maria%’

  19. DELETE Details • DELETE FROM Customers WHERE CustomerID = ‘ANATR’ • Make sure the criteria is correct. If no criteria is included, then all data will be deleted.

  20. DELETE Summary • Using the DELETE statement, we can delete data in our database. • For example, to delete the customer with ID ‘ANATR’, use the following code: DELETE FROM Customers WHERE CustomerID = ‘ANATR’

  21. Comprehension Check DELETE Statement

More Related