Download SQL-Introduction to Database Systems-Lecture Handouts and more Lecture notes Introduction to Database Management Systems in PDF only on Docsity!
Introduction to Database Systems
SQL Task Lab 2
Create Table:
- General Query:
CREATE TABLE "table_name"
("column 1" "data_type_for_column_1", "column 2" "data_type_for_column_2",
... )
2. Table with Fields Values
CREATE TABLE customer
(First_Name char(50), Last_Name char(50),
Address char(50), City char(50),
Country char(25), Birth_Date date)
3. Specifying Primary Key
CREATE TABLE Customer (SID integer PRIMARY KEY,
Last_Name varchar(30), First_Name varchar(30));
4. Specifying Not NULL Constraint
CREATE TABLE Customer
(SID integer NOT NULL, Last_Name varchar (30) NOT NULL,
First_Name varchar(30));
5. Specifying Unique Constraint
CREATE TABLE Customer (SID integer Unique, Last_Name varchar (30), First_Name varchar(30));
6. Specifying Check Constraint
CREATE TABLE Customer (SID integer CHECK (SID > 0),
Last_Name varchar (30), First_Name varchar(30))
7. Specifying Foreign Key Constraint
CREATE TABLE ORDERS
(Order_ID integer primary key, Order_Date datetime, Customer_SID integer references CUSTOMER(SID),
Amount double);
Insert Query:
1. General Query:
INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)
2. Specifying values and columns
INSERT INTO Store_Information (store_name, Sales, Date) VALUES ('Los Angeles', 900, 'Jan‐ 10 ‐1999')
Update Query
1. General Query
UPDATE "table_name" SET "column_1" = [new value] WHERE {condition}
2. Specifying Update Column
UPDATE Store_Information SET Sales = 500 WHERE store_name = "Los Angeles" AND Date = "Jan‐ 08 ‐1999"
SQL Results:
id customer day_of_order product quantity
1 Tizag 2008-08-01 00:00:00.000 Pen 4
2 Tizag 2008-08-01 00:00:00.000 Stapler 1
5 Tizag 2008-07-25 00:00:00.000 19" LCD Screen 3
6 Tizag 2008-07-25 00:00:00.000 HP Printer 2
Conditional statements are not unique to SQL, and neither are operators. Operators are symbols such as (=) or (<), and they are seen inside of conditional statements and expressions in SQL and other programming languages. While we're not going to dive into much detail about the different kinds of operators yet, it is a good idea to be familiar with them and be able to recognize them inside of conditional statements as we look over the next few examples.
SQL - Where Queries
With the WHERE clause on our tool belts, we can be more creative when querying for table rows. For instance, there may come a time where we would like to take a look at all the orders placed after a certain date.
SQL Where Date Query:
USE mydatabase;
SELECT * FROM orders WHERE day_of_order > '7/31/08'
This conditional statement will return only the orders that have made it into the table since the end of July, filtering out any orders in the table made prior to July 31st^.
SQL Results:
id customer day_of_order product quantity
1 Tizag 2008-08-01 00:00:00.000 Pen 4
2 Tizag 2008-08-01 00:00:00.000 Stapler 1
3 A+Maintenance 2008-08-16 00:00:00.000 Hanging Files 12
4 Gerald Garner 2008-08-15 00:00:00.000 19" LCD Screen 3
Notice how the date value is formatted inside the conditional statement. We passed a value formatted MM/DD/YY, and we've completely neglected the hours, minutes, and seconds values, yet SQL is intelligent enough to understand this. Therefore, our query is successfully executed.
SQL - Where with Multiple Conditionals
A WHERE statement can accept multiple conditional statements. What this means is that we are able to select rows meeting two different conditions at the same time.
Perhaps the easiest way to go about this is to add another condition to the previous example, where we retrieved only the orders placed after July 31st^. We can take this example one step further and link two conditional statements together with "AND".
SQL Where And:
USE mydatabase;
SELECT * FROM orders WHERE day_of_order > '7/31/08' AND customer = 'Tizag'
At this point, we have sent SQL two conditional statements with a single WHERE clause, essentially applying two filters to the expected result set.
SQL Results:
id customer day_of_order product quantity
1 Tizag 2008-08-01 00:00:00.000 Pen 4
2 Tizag 2008-08-01 00:00:00.000 Stapler 1
By applying the AND clause, SQL has now been asked to return only rows that meet both conditional statements. In this case, we would like to return all orders that were made before July 31 st^ and made by a specific company - which is, in this case, Tizag. We have more examples of SQL AND/OR. Just follow the link.
SQL - And
SQL AND links together two or more conditional statements for increased filtering when running SQL commands. AND helps the developer query for very specific records while answering questions like, "I want to view all orders made by a certain customer AND made on a
5 Tizag 2008-07-25 00:00:00.000 19" LCD Screen 3
The first record returned matches the first condition since the product = 'Pen'. The two records after that match the other condition; the product in each of those orders is the '19" LCD Screen'. This type of logic allows the developer to filter results based on one or more conditions.
SQL AND allows the developer to query for more specific data by linking together conditional statements. On the other end of the spectrum, SQL OR creates a new, independent condition not linked to any other conditional statement.
SQL - And / Or Combination
Conditional statements can be grouped together using parentheses (). Doing so links together conditions and provides robust solutions for data querying.
SQL And/Or Code:
USE mydatabase;
SELECT * FROM orders WHERE (quantity > 2 AND customer = 'Tizag') OR (quantity > 0 AND customer = 'Gerald Garner')
By encapsulating the first two conditions (quantity > 2 AND customer = 'Tizag') SQL now treats this as a single condition, and the same is true for the next line. These two conditions have been linked together with the OR operator, creating very unique behavior.
SQL is now looking for rows where the customer is Tizag AND the quantity is more than 2 , and ALSO looking for rows where the customer is Gerald Garner AND the quantity is greater than 0. All rows meeting either condition will be returned as demonstrated in our results below.
SQL Results:
id customer day_of_order product quantity
1 Tizag 2008-08-01 00:00:00.000 Pen 4
4 Gerald Garner 2008-08-15 00:00:00.000 19" LCD Screen 3
5 Tizag 2008-07-25 00:00:00.000 19" LCD Screen 3
SQL - Between
BETWEEN is a conditional statement found in the WHERE clause. It is used to query for table rows that meet a condition falling between a specified range of numeric values. It would be used to answer questions like, "How many orders did we receive BETWEEN July 20 th^ and August 5 th?"
SQL Select Between:
USE mydatabase;
SELECT * FROM orders WHERE day_of_order BETWEEN '7/20/08' AND '8/05/08';
SQL Results:
id customer day_of_order product quantity
1 Tizag 2008-08-01 00:00:00.000 Pen 4
2 Tizag 2008-08-01 00:00:00.000 Stapler 1
5 Tizag 2008-07-25 00:00:00.000 19" LCD Screen 3
6 Tizag 2008-07-25 00:00:00.000 HP Printer 2
BETWEEN essentially combines two conditional statements into one and simplifies the querying process for you. To understand exactly what we mean, we could create another query without using the BETWEEN condition and still come up with the same results, (using AND instead).
SQL Select Between:
USE mydatabase;
SELECT * FROM orders WHERE day_of_order >= '7/20/08' AND day_of_order <= '8/05/08';
SQL Results:
id customer day_of_order product quantity
1 Tizag 2008-08-01 00:00:00.000 Pen 4
2 Tizag 2008-08-01 00:00:00.000 Stapler 1
5 Tizag 2008-07-25 00:00:00.000 19" LCD Screen 3
6 Tizag 2008-07-25 00:00:00.000 HP Printer 2
USE mydatabase;
SELECT * FROM orders WHERE customer = 'Tizag' ORDER BY day_of_order DESC
SQL Results:
id customer day_of_order product quantity
1 Tizag 2008-08-01 00:00:00.000 Pen 4
2 Tizag 2008-08-01 00:00:00.000 Stapler 1
5 Tizag 2008-07-25 00:00:00.000 19" LCD Screen 3
6 Tizag 2008-07-25 00:00:00.000 HP Printer 2
If you compare these results to the results above, you should notice that we've pulled the same information but it is now arranged in a reverse (descending) order.
SQL - Sorting on Multiple Columns
Results may be sorted on more than one column by listing multiple column names in the ORDER BY clause, similar to how we would list column names in each SELECT statement.
SQL Order by Multiple columns:
USE mydatabase;
SELECT * FROM orders ORDER BY customer, day_of_order;
This query should alphabetize by customer, grouping together orders made by the same customer and then by the purchase date. SQL sorts according to how the column names are listed in the ORDER BY clause.
SQL Results:
id customer day_of_order product quantity
3 A+Maintenance 2008-08-16 00:00:00.000 Hanging Files 12
4 Gerald Garner 2008-08-15 00:00:00.000 19" LCD Screen 3
5 Tizag 2008-07-25 00:00:00.000 19" LCD Screen 3
6 Tizag 2008-07-25 00:00:00.000 HP Printer 2
1 Tizag 2008-08-01 00:00:00.000 Pen 4
2 Tizag 2008-08-01 00:00:00.000 Stapler 1
SQL - Alter
SQL ALTER is the command used to add, edit, and modify data objects like tables, databases, and views. ALTER is the command responsible for making table column adjustments or renaming table columns. New table columns can also be added and dropped from existing SQL tables.
SQL Add:
USE mydatabase;
ALTER TABLE orders ADD discount VARCHAR(10);
SQL Results:
id customer day_of_order product quantity discount
1 Tizag 2008-08-01 00:00:00.000 Pen 8 NULL
2 Tizag 2008-08-01 00:00:00.000 Stapler 3 NULL
3 A+Maintenance 2008-08-16 00:00:00.000 Hanging Files 14 NULL
4 Gerald Garner 2008-08-15 00:00:00.000 19" LCD Screen 5 NULL
5 Tizag 2008-07-25 00:00:00.000 19" LCD Screen 5 NULL
6 Tizag 2008-07-25 00:00:00.000 HP Printer 4 NULL
As you can see from the results panel, SQL has added an additional column, discount, to the orders table. Since this column was just created, it contains no data, and only NULL values have been returned.
SQL - Alter Table: Modify Column
SQL table columns can be altered and changed using the MODIFY COLUMN command. This allows the developer the opportunity to mold table columns or adjust settings as needed.
SQL Modify Column:
USE mydatabase;
ALTER TABLE orders
Now we can throw this query into the WHERE clause of another SELECT query and obtain the results to our little dilemma.
SQL Select Subquery Code:
USE mydatabase;
SELECT * FROM orders WHERE day_of_order = (SELECT MAX(day_of_order) FROM orders)
id customer day_of_order product quantity
3 A+Maintenance 2008-08-16 00:00:00.000 Hanging Files 14
This query is a dynamic query as it pulls current information and will change if a new order is placed. Utilizing a subquery we were able to build a dynamic and robust solution for providing us with current order information.
SQL - Join
SQL JOIN joins together two tables on a matching table column, ultimately forming one single temporary table. The key word here is temporary. The tables themselves remain intact, and running a JOIN query does not in any way change the data or table structure. JOIN is another way to select specific data from two or more relational tables.
In order to perform a JOIN query, we need a few pieces of information: the name of the table and table column we want to join on and a condition to meet for the JOIN to happen. This should sound a little confusing as there is much going on in a JOIN query, so let's take a look at an example:
SQL Join Query Code:
USE mydatabase;
SELECT * FROM orders JOIN inventory ON orders.product = inventory.product;
SQL Join Results:
id customer day_of_order product quantity id product quantity price
1 Tizag
Hanging Files
Hanging Files
2 Tizag
Stapler 3 4 Stapler 3 7.
3 A+Maintenance
Hanging Files
Hanging Files
4 Gerald Garner
19" LCD
Screen
19" LCD
Screen
5 Tizag
19" LCD
Screen
19" LCD
Screen
6 Tizag 2008-07- 00:00:00. HP Printer 4 2 HP Printer 9 89.
The line beginning with JOIN (Line 4) is where we tell SQL which table we would like to join. The next line (Line 5) is a different story. Here is where we have specified the condition to JOIN ON. In this case, both tables have identical product columns which makes them an ideal target for a join. Basically we are temporarily merging the tables connecting them where they match, the product column.
This type of join matches values from one table column with a corresponding value in another table and uses that match to merge the tables together. In our make-believe store world, this let's us join the inventory table with the orders table to show us all the items we currently have in stock for our customers and also the price of each item.
Let's rework this query a bit and strip away a few of the table columns to make our results easier to read and understand. We will replace the (*) parameter with a list containing only the table columns we are interested in viewing.
SQL Join:
USE mydatabase;
SELECT orders.customer, orders.day_of_order, orders.product, orders.quantity as number_ordered, inventory.quantity as number_instock, inventory.price FROM orders JOIN inventory ON orders.product = inventory.product
SQL Results:
customer day_of_order product number_ordered number_instock price
NULL NULL NULL NULL NULL 3 Pen 78 0.
2 Tizag
Stapler 3 4 Stapler 3 7.
1 Tizag
Hanging Files
Hanging Files
3 A+Maintenance 2008-08- 00:00:00.
Hanging Files 14 5 Hanging Files
NULL NULL NULL NULL NULL 6 Laptop 16 499.
You should see a new row at the bottom of the results box with a bunch of NULL values. This is a result of the RIGHT JOIN and is the intended result from running the query. We end up with an extra row because inside of the inventory table, the Laptop item was not joined with a product from the orders table. This just means that we have not sold a laptop as of yet and it shouldn't be much a surprise since we already know from querying the orders table in previous lessons that there have been no laptop orders so far.
By specifying RIGHT JOIN , we have told SQL to join together the tables even if no matches are found in the conditional statement. All records that exist in the table on the right side of the conditional statement (ON orders.product = inventory.product) will be returned and NULL values will be placed on the left if no matches are found.
SQL - Left Join
SQL LEFT JOIN works exactly the same way as RIGHT JOIN except that they are opposites. NULL values will appear on the right instead of the left and all rows from the table on the left hand side of the conditional will be returned.
Unfortunately, we will not be able to show a very intuitive example of a LEFT JOIN because of how our tables are structured. The orders table should always have a matching inventory item and if not, that means we are in big trouble as we could be selling items we do not carry in inventory. For good measure, here's what a LEFT JOIN would look like:
SQL Left Join:
USE mydatabase;
SELECT * FROM orders LEFT JOIN inventory ON orders.product = inventory.product
SQL JOIN is intended to bring together data from two tables to form a single larger table, and often, it will paint a more detailed picture of what the data represents. By merging these two data
sets, we were able to peer into our database and ensure that each item ordered so far is in stock and ready to be shipped to our customers.
SQL - In
SQL IN is an operator used to pull data matching a list of values. A scenario where this proves useful would be if we wanted to retrieve customer data for two or more customers. We can use the IN operator to specify a list of customer names, and SQL will retrieve rows reflecting every customer in the list.
Inside the query statement itself, the word " IN " replaces the (=) operator after the WHERE declarative and slightly alters the meaning as well. Instead of listing a single value, you may list multiple values and SQL will retrieve the results for each value listed.
SQL In:
USE mydatabase;
SELECT * FROM orders WHERE customer IN ('Gerald Garner','A+Maintenance');
SQL Results:
id customer day_of_order product quantity
3 A+Maintenance 2008-08-16 00:00:00.000 Hanging Files 14
4 Gerald Garner 2008-08-15 00:00:00.000 19" LCD Screen 5
The results provide a list of all customer orders made by each of the customer names we have listed inside the IN clause ('Gerald Garner','A+Maintenance'). This is a great way to query for all orders made by a handful of different customers as we can see everything these particular customers have ordered thus far.
The real power of this condition comes to life when used with a subquery that retrieves a list of values. Running any SELECT query returns results in list format. And as we mentioned just a few short moments ago, this list can then be passed as a list for the IN clause using a subquery.
Let's adjust the previous example to only retrieve only the products column, as opposed to retrieving all columns (*).
SQL In:
USE mydatabase;
USE mydatabase;
SELECT * FROM inventory WHERE product NOT IN (SELECT product FROM orders WHERE customer IN ('Gerald Garner','A+Maintenance'));
SQL Results:
id product quantity price
2 HP Printer 9 89.
3 Pen 78 0.
4 Stapler 3 7.
6 Laptop 16 499.
SQL - Case
SQL CASE is a very unique conditional statement providing if/then/else logic for any ordinary SQL command, such as SELECT or UPDATE. It then provides when-then-else functionality (WHEN this condition is met THEN do_this).
This functionality provides the developer the ability to manipulate the presentation of the data without actually updating or changing the data as it exists inside the SQL table.
SQL Select Case Code:
USE mydatabase;
SELECT product, 'Status' = CASE WHEN quantity > 0 THEN 'in stock' ELSE 'out of stock' END FROM dbo.inventory;
SQL Results:
product Status
19" LCD Screen in stock
HP Printer in stock
Pen in stock
Stapler in stock
Hanging Files in stock
Laptop in stock
Using the CASE command, we've successfully masked the actual value of the product inventory without actually altering any data. This would be a great way to implement some feature in an online catalog to allow users to check the status of items without disclosing the actual amount of inventory the store currently has in stock.
SQL - Case: Real World Example
As a store owner, there might be a time when you would like to offer sale prices for products. This is a perfect opportunity to write a CASE query and alter the inventory sale prices at the presentation level rather than actually changing the price inside of the inventory table. CASE provides a way for the store owner to mask the data but still present it in a useful format.
Let's back up a second and pull a listing of our recent orders and join this with the inventory table so that the results contain both the quantity of items purchased and the price from the inventory table. To accomplish this we will need to first write a SQL JOIN query.
SQL Join Query:
USE mydatabase;
SELECT dbo.orders.id, dbo.orders.customer, dbo.orders.quantity, dbo.inventory.product, dbo.inventory.price FROM orders JOIN inventory ON orders.product = inventory.product
In order to provide results that are much clearer, we've moved away from selecting every column with (*). Instead, we've listed each column that will be of use for the next few steps. Also, let's plan on offering a 25%^ off sale on these items.
SQL Results:
id customer quantity product price
1 Tizag 11 Hanging Files 14.
2 Tizag 3 Stapler 7.
3 A+Maintenance 14 Hanging Files 14.
