Structured Query Language

1. Structured Query Language SQL: An Introduction

2. SQL (Pronounced S.Q.L) • The standard user and application program interface to a relational database is the Structured Query Language (SQL) • SQL statements are used both for interactive queries for information from a relational database and for gathering data for reports • SQL uses keywords to interact with databases • It allows creating a database, populating, and querying it • In this slide we learn to apply MySQL Workbench to do all of these • You can also use MySQL client

3. Create a Database/Table • The syntax to create a database : CREATE DATABASE databaseName//Comment: SQL is case sensitive! • The CREATE keyword is also used to create a table CREATE TABLE tablename//Capitalize keywords for clarity!( Column1 description, //define each column Column2 description, // put a comma after each … ) Note: text written after // is comment, and is NOT part of the syntax!

4. Use MySQL to name the database • Connect to MySQL // Read Chapter 4 Ullman • Make sure you have permission to create database CREATE DATABASE sitename; // end each statement with a ; USE sitename; // tells MySQL that you want to use the database from here on

5. Create the users Table in the Database CREATE TABLE user ( User_idMEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, first_nameVARCHAR(20) NOT NULL, Last_nameVARCHAR(40) NOT NULL, email VARCHAR(60) NOT NULL, pass CHAR(40) NOT NULL, registration_dateDATETIME NOT NULL, PRIMARY KEY (user_id), ); NOTE: The user table was designed in Chapter 4 Ullman’s book!

6. Confirm that the table exists • SHOW TABLES; • SHOW CCOLUMNS FROM users;

7. Populating the table • Now that the table exists, insert records into it • For this purpose we use the INSERT syntax: • When we want only to enter values for certain columns: INSERT INTOtablename (column1, column2, …) VALUES (value1, value2, …); // Columns not given a value will get NULL unless they have a default // value. If they are NOT NULL and have no default there will be an error • When we want to enter values for all columns use the following: INSERT INTOtablenameVALUES (value1, NULL, value2, value3, …)

8. Inserting multiple rows (records) INSERT INTO tablename (column1, column2) VALUES (valueA, valueB), (valueC, valueD), (valueE, valueF), // this is not supported by all database systems (only by MySQL)

9. Insert real data into the users table INSERT INTO users (first_name, last_name, email, pass, registration_date,) VALUES (‘Hassan’, ‘Babaie’, ‘mylonite_7@yahoo.com’, SHA1(‘mypassword’), NOW()); //or without naming columns INSERT INTO users VALUES (NULL, ‘Hassan’, ‘Babaie’, ‘mylonite_7@yahoo.com’, SHA1(‘mypassword’), NOW()); // note: NULL is for the user_id (since all values must be provided!) //Comments: // note: put strings in “quotation marks”! // char, varchar, and text are strings! // NULL should not be quoted // backslash escapes a mark, e.g., O’clock is written as ‘O\’clock’ //the SHA1() and NOW() are functions. //the SHA1() encripts data to a 40 character long string, hence //CHAR(40) is used for password

10. Joins • Table joins are used to query data from two or more tables, based on a relationship between certain columns in these tables • Tables in a database are often related to each other with keys

12. To get the sales information by region, we have to combine the information from the two tables • These two tables are linked by the common field, "store_name“ • The following SQL will retrieve the sales data by the region. SELECT A1.region_name REGION, SUM(A2.Sales) SALES FROM Geography A1 JOIN Store_Information A2 ON A1.store_name = A2.store_name GROUP BY A1.region_name • Result:

13. Join • JOIN: Return rows when there is at least one match in both tables • LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table • RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table • FULL JOIN: Return rows when there is a match in one of the tables

14. Views • A Database View is a subset of the database sorted and displayed in a particular way • For each view, you can control which columns are displayed, what order they are displayed in, how the data is sorted, and what types of records to display • A view consists of rows and columns just like a table • The difference between a view and a table is that views are definitions built on top of other tables (or views), and do not hold data themselves • If a data item is changing in the underlying table, the same change is reflected in the view • A view can be built on top of a single table or multiple tables. It can also be built on top of another view

15. View Customer Table • CREATE TABLE Customer(First_Namevarchar(100),Last_Namevarchar(100), Address varchar(50), City varchar(30), Country varchar(25),Birth_Date date NOT NULL) • Use the following syntax to create a view called V_Customer that contains only the First_Name, Last_Name, and Country columns from this table CREATE VIEW V_CustomerAS SELECT First_Name, Last_Name, CountryFROM Customer • Now we have a view called V_Customer with the following structure: View V_Customer(First_Name char(100),Last_Name char(100),Country char(25))

16. Alter Table • Alter Table - Once a table is created in the database, you can use the Alter statement to change the structure of the table • For example: • Add a column • Drop a column • Change a column name • Change the data type for a column ALTER table Customer add Gender char(1) ALTER table Customer drop Country

17. Drop Table • Drop Table - Sometimes we may need to delete a table in the database • This allows to get rid of tables not needed anymore and frees up database space • Use the following command to delete the Customer table. DROP TABLE Customer

18. Truncate Table • TRUNCATE TABLE - Sometimes we may need to delete all of the data in a table • One way of doing this is with DROP TABLE • But what if we wish to simply get rid of the data but not the table itself? • For this, we can use the TRUNCATE TABLE command • Use the following command to delete all data in Customer table. TRUNCATE TABLE Customer

19. Insert Into • INSERT INTO Statement – Insert statement is used to add one or more rows to a table INSERT INTO Customer values ('4','Adams','Joe') – adds one row INSERT INTO Store_Information (store_name, Sales, Date)SELECT store_name, Sales, DateFROM Sales_InformationWHERE Year(Date) = 1998

20. Update • UPDATE Statement - allows to update data in an existing table UPDATE Store_InformationSET Sales = 2000WHERE store_name = "Los Angeles"AND Date = "Jan-08-1999"

21. Delete • DELETE Statement – allows to remove rows from a table DELETE FROM Store_InformationWHERE store_name = "Los Angeles"

22. Stored procedures • A stored procedure is executable code that is associated with, and generally stored in, the database • Stored procedures usually collect and customize common operations, like inserting a row into a table, gathering statistical information about usage patterns, or encapsulating complex business logic and calculations