W3 SQL Tutorial PART 1: SQL Intro - SQL Between

What is a column in the table

A vertical entity in a table that contains all information associated with a specific field in a table.

What is a RECORD in the table

Also called a row, is each individual entry that exists in a table. For example, there are 5 records in the above Customers table. A record is a horizontal entity in a table.

What is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

By making sure I have a semicolon at the end of each SQL statement. A SEMICOLON IS REQUIRED AT THE END OF EACH STATEMENT IN ORDER FOR IT TO WORK

SQL DELETE Example: The following statement deletes the customer Alfreds Futterkiste from the Customers table:

DELETE FROM Customers WHERE CustomerName= 'Alfreds Futterkiste';

SQL DELETE test yourself with exercises: Give Ex for the statement below: Delete all the records from the Customers table where the Country value is 'Norway'.

DELETE FROM Customers WHERE Country = 'Norway';

What is the DELETE Syntax?

DELETE FROM table_name WHERE condition;

What is the syntax for deleting all the rows in a table without deleing the table? Give Ex for the statement below The following SQL statement deletes all rows in the "Customers" table without deleting the table:

DELETE FROM table_name; Ex. DELETE FROM Customers;

You can also combine the AND, OR and NOT operators. Give (2) Examples of combining all Ex 1: Select all fields from 'Customers' where country is 'Germany' and city must be "Berlin" or "Muchen" (use parenthesis to form complex expressions): Ex 2: Select all fields from 'Customers' where country is NOT "Germany" and NOT "USA":

Example 1 Answer: SELECT * FROM customers WHERE country = 'Germany' AND (City = 'Berlin' OR City ='Muchen') ; Example 2 Answer: SELECT * FROM Customers WHERE NOT country = 'Germany' AND NOT country = 'USA';

Give example of Way 2: (Adding values for all the columns of the table, I do not need to specify the column names in the SQL query) Insert a new record in the Customers table Enter Values: Cardinal Tom B Erichsen Skagen 21 Stavanger 4006 Norway

INSERT INTO Customers VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

Give example of Way 1: (Specify both the column names and the values to be inserted. ) Insert a new record in the Customers table for columns: CustomerName Address City PostalCode Country Enter Values: Hekkan Burger Gateveien 15 Sandnes 4306 Norway

INSERT INTO Customers (CustomerName, Address, City, PostalCode, Country) VALUES('Hekkan Burger', 'Gateveien 15', 'Sandnes', '4306', 'Norway');

Inserting Data Only in Specified Columns: It is also possible to only insert data in specific columns. Give Ex. Write the statement to insert a new record in the Customers table only to the CustomerName, City and Country Columns.

INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');

ORDER BY Several Columns Example 2: Write the SQL syntax for the following statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column:

SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC; *Remember when using ascending and descending together to add what column I want to ascend and what column I want to descend.

ORDER BY Several Columns Example: Write the SQL syntax for the following statement selects all customers from the "Customer" table, sorted by the "Country" and the "CustomerName" column. This mean that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:

SELECT * FROM Customers ORDER BY Country, CustomerName;

ORDER BY Example: Write the SQL syntax for the following statement that selects all customers from the "Customers" table, sorted by "Country" column:

SELECT * FROM Customers ORDER BY Country;

SQL NULL: Exercise 2 Select all records from the Customers where the PostalCode column is NOT empty:

SELECT * FROM Customers WHERE PostalCode IS NOT NULL;

How would I write the following statement? Select all the customers from the country "Mexico", in the "Customers" table:

SELECT * FROM Customers WHERE Country='Mexico';

What Select Syntax would we use if you wanted to select ALL columns/fields from a table. Give Ex.

SELECT * FROM table_name; SELECT * FROM Customers;

What is the Most Important SQL Commands (11)

SELECT - extracts data from a database UPDATE - updates data in a database DELETE - deletes data from a database INSERT INTO - inserts new data into a database CREATE DATABASE - creates a new database ALTER DATABASE - modifies a database CREATE TABLE - creates a new table ALTER TABLE - modifies a table DROP TABLE - deletes a table CREATE INDEX - creates an index (search key) DROP INDEX - deletes an index

The IS NOT NULL operator is used to test for non-empty values (NOT NULL values) Write the SQL statement for the following: List all customers with a value in the "Address" field:

SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NOT NULL;

The IS NULL operator is used to test for empty values (NULL VALUES) Give Ex. Write the SQL statement for the following: Lists all customers with a NULL value in the "Address" field:

SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NULL;

What is the Syntax for SELECT DISTINCT? Give Ex.

SELECT DISTINCT column1, column2, ...FROM table_name; Ex. SELECT DISTINCT Country, FROM Customers; (insert image) the original table before the SELECT DISTINCT statement shows Mexico twice under the country column/field but because we used the SELECT DISTINCT statement it only have Mexico listed once. So with the SELECT DISTINCT command it gets rid of the duplicates.

What is the AND Syntax Give Ex. Select all fields from 'Customers' where country 'Germany' AND city is 'Berlin';

SELECT column 1, column 2 FROM table_name WHERE condition 1 AND condition 2 AND condition 3...; Ex. Answer SELECT * FROM Customers WHERE Country= 'Germany' AND City= 'Berlin';

What is the WHERE keyword Syntax Give example: The following SQL statement selects CustomerID, PostalCode from the city London in the "Customers" table:

SELECT column 1, column 2 FROM table_name WHERE conditions Ex. SELECT CustomerID, PostalCode FROM customers WHERE City = 'London';

What is the ORDER BY syntax

SELECT column 1, column 2, .. FROM table_name ORDER BY column1, column2, ...ASC/DESC;

What is the NOT Syntax Give Ex. Give statement for the following SQL statement select all field from "Customers" where country is NOT "Germany":

SELECT column1 , column 2 FROM table_name WHERE NOT condition; Ex. Answer SELECT * FROM Customers WHERE NOT country= 'Germany';

What is the OR Syntax Give Ex. Select all fields from "Customers" where city is "Berlin" OR "Muchen":

SELECT column1, column 2 FROM table_name WHERE condition 1 OR condition 2 OR condition 3...; Ex Answer: SELECT * FROM Customers WHERE City= 'Berlin' OR City= 'Muchen';

What is the SELECT syntax you would use when you want to select from specific columns/fields? Give Ex.

SELECT column1, column2, FROM table_name; Ex. SELECT CustomerName, City FROM Customers; (inset image)

What is the IS NULL Syntax?

SELECT column_names FROM table_name WHERE Column_name IS NULL;

What is the NOT NULL Syntax?

SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

SQL NULL: Exercise 1 Select all records from the Customers where the PostalCode column is empty:

SELECT* FROM Customers WHERE PostalCode IS NULL;

What statements can we use WHERE

SELECT, UPDATE, DELETE.. etc

When using the WHERE clause we can use operations to help tell us where exactly we want to get information from. What are the Operators in The WHERE Clause and give there meaning.

OperatorDescriptionExample = Equal > Greater than <Less than >=Greater than or equal <=Less than or equal <>Not equal. Note: In some versions of SQL this operator may be written as != BETWEEN Between a certain range LIKE Search for a pattern INTO specify multiple possible values for a column

What Can SQL do?

SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views

What is the rule for Text Fields vs. Numeric Fields

SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes: Ex.Text value command SELECT * FROM Customers WHERE Country='Mexico'; Ex. Numeric Field command SELECT * FROM CustomersWHERE CustomerID=1;

What is SQL?

SQL stands for Structured Query Language. SQL lets you access and manipulate databases.

Most of the actions you need to perform on a database are done with? Give Ex.

SQL statements. Example: The following SQL statement selects all the records in the "Customers" table: SELECT * FROM Customers;

What operators are used to filter records based on more than one condition? Give Ex.

The AND and OR operators are used to filter records based on more than one condition The AND operator displays a record if all the conditions separated by AND are TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE.

What operator displays a record if the condition(s) are not TRUE

The NOT operator displays a record if the condition(s) is Not TRUE

SQL UPDATE STATEMENT: What is the UPDATE statement used for?

The UPDATE statement is used to modify the existing records in a table.

Where clause can be combined with what operators ?

The WHERE clause can be combined with AND, OR, and NOT operators.

What is the WHERE clause used for?

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

When do we use the SELECT command

The statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

SQL UPDATE: Test Yourself Exercise #1 Update the City column of all records in the Customers table to Oslo

UPDATE Customers SET City= 'Oslo';

Give Example of the UPDATE Syntax using the statement below Updates the first customer (CustomerID=1) with a new contact person Alfred Schmidt and a new city Frankfurt

UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID= 1;

REMEMBER when updating records if I omit the WHERE clause ALL records will be updated. Give Example of what will happen if I omit the WHERE clause.. Update the ContactName column to Juan of all records in the Customers table. (DO Not add WHERE and look at the image)

UPDATE Customers SET ContactName= 'Juan';

SQL UPDATE: Test Yourself Exercise #2 In the Customers table Set the value of the City columns to 'Oslo', but only the ones where the Country column has the value "Norway".

UPDATE Customers SET City= 'Oslo' WHERE Country = 'Norway';

What is the Syntax for the UPDATE statement?

UPDATE table_name SET column1 = value 1, column2= value 2,... WHERE condition;

What can we do if we want to know how many DISTINCT information is listed under the column/field? Give Ex.

We would use the the SELECT COUNT statement Ex. SELECT COUNT(DISTINCT Country) FROM Customers; *make sure I put the distinct part in parenthesis when using the SELECT COUNT statement. When I run it its counts 21 (insert image)

What is A field

is a column in a table that is designed to maintain specific information about every record in the table. *The fields in the Customers table consist of CustomerID, CustomerName, ContactName, Address, City, PostalCode and Country. (view image)

SQL is a standard language for RDBMS however...

to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner. Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!

INSERT INTO Statement: What is the insert statement used for?

The INSERT INTO statement is used to insert new records in a table.

How is each table identified ?

Each table is identified by the Name it was given when it was created. Which contains records (rows) with data.

Every table is broken up into smaller entities called

Fields

A database most often contains what?

One or more tables

What does RDBMS stand for? And what is it?

Relational Database management system. It is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

Break down the customer table in the image

* The table in the image contains five records (one for each customer) and seven columns (CustomerID, CustomerName, ContactName, Address, City, PostalCode, and Country). (input image)

SQL NULL VALUE: What is a Null Value? (4) Explain..

*A field with a NULL value is a field with no value. *It is different than a zero value or field that contains spaces. NULL means nothing at all just a place holder. *A field with a NULL value is one that has been left blank during record creation. *If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

When using SQL to build your website that shows data from the database what will you need.

*An RDBMS database program (i.e. MS Access, SQL Server, MySQL) *To use a server-side scripting language, like PHP or ASP *To use SQL to get the data you want *To use HTML / CSS to style the page

Caveate: What should we be careful of when deleting records in a table?

*Be careful when deleting records in a table! *Notice the WHERE clause in the DELETE statement. *The WHERE clause specifies which records(s) should be deleted. *If you omit the WHERE clause, all records in the table will be deleted!

Caveat when it comes to updating records in a table.. (2)

*Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which records that should be updated. If you omit the WHERE clause, all records in the table will be updated. *It is the WHERE clause that determines how many records will be updated.

How do we Test for NULL Values(4)

*We will have to use the IS NULL and IS NOT NULL operators to test for NULL Values. *The IS NULL operator is used to test for empty values (NULL VALUES) *The IS NOT NULL operator is used to test for non-empty values (NOT NULL values). It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

1. When would we use the SELECT DISTINCT statement? 2.Example:

1. Used to return only distinct (different) values. Not wanting duplicates.. 2. For Example.. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values. This is when you would use the SELECT DISTINCT statement.

What is a table? Ex.

A collection of related data entries and it consists of columns and rows. Ex. (insert table)

Where is the Data in the database stored

It is stored in database objects called tables

Caveate when it comes to INSERT INTO: Why did I not need to include the CustomerID column in my statement?

Remember: The CustomerID or primary key is an auto-increment field and will be generated automatically when a new record is inserted into the table. So I do not need to include it.

SQL DELETE statement: What is the delete statement used for?

The DELETE statement is used to delete existing records in a table.

SQL ORDER BY: What is the ORDER BY keyword used for?

The ORDER BY keyword is used to sort the result-set in ascending and/or descending order.

What is the rule when it comes to ORDER BY keyword being sorted in ascending order?

The ORDER BY keyword sorts the records in ascending order by default.

How would I sort the records in descending order when using the ORDER BY keyword?

To sort the records in descending order, I would use the DESC keyword.

What are the 2 ways I can write INSERT INTO statement? Way 1 and Syntax: Way 2 and Syntax:

Way 1: Specify both the column names and the values to be inserted: INSERT INTO table_name (Column 1, Column 2, Column 3,..) VALUES (value1, value2, value3, ....); Way 2: If I am adding value for all the columns of the table, I do not need to specify the column names in the SQL query. However, make sure the order of the value is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows: INSERT INTO table_name VALUES (value1, value2, value3,...);

SQL DELETE ALL RECORDS: Is it possible to delete all rows in a table without deleting the table?

Yes.. it possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact: