- Information
- AI Chat
SQL- Tutorial asdsa dsadsadsadsadsadsadsadsa dsadsadsa
Statistics (STAT 101)
University of Mindanao
Recommended for you
Preview text
Introduction to SQL
« Previous
Next Chapter »
SQL is a standard language for accessing and manipulating databases.
What is SQL?
####### SQL stands for Structured Query Language
####### SQL lets you access and manipulate databases
####### SQL is an ANSI (American National Standards Institute) standard
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
SQL is a Standard - BUT....
Although SQL is an ANSI (American National Standards Institute) standard, there are different versions of the SQL language.
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.
Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!
Using SQL in Your Web Site
To build a web site that shows data from a database, you will need:
####### An RDBMS database program (i. 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
RDBMS
RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
The data in RDBMS is stored in database objects called tables.
A table is a collection of related data entries and it consists of columns and rows.
SQL Syntax
« Previous
Next Chapter »
Database Tables
A database most often contains one or more tables. Each table is identified by a name (e. "Customers" or "Orders"). Tables contain records (rows) with data.
In this tutorial we will use the well-known Northwind sample database (included in MS Access and MS SQL Server).
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCodeCountry
Some of The Most Important SQL Commands
####### 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 SQL SELECT Statement
The SELECT statement is used to select data from a database.
The result is stored in a result table, called the result-set.
SQL SELECT Syntax
####### SELECT column_name , column_name
####### FROM table_name ;
and
####### SELECT * FROM table_name ;
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCodeCountry
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados
Ana Trujillo Avda. de la Constitución 2222
México D.
05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.
05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund
Berguvsvägen 8 Luleå S-958 22 Sweden
SELECT Column Example
The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table:
Example
####### SELECT CustomerName,City FROM Customers;
Try it yourself »
SELECT * Example
The following SQL statement selects all the columns from the "Customers" table:
Example
####### SELECT * FROM Customers;
Try it yourself »
SELECT DISTINCT Example
The following SQL statement selects only the distinct values from the "City" columns from the "Customers" table:
Example
####### SELECT DISTINCT City FROM Customers;
Try it yourself »
SQL WHERE Clause
« Previous
Next Chapter »
The WHERE clause is used to filter records.
The SQL WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion.
SQL WHERE Syntax
####### SELECT column_name , column_name
####### FROM table_name
####### WHERE column_name operator value ;
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCodeCountry
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados
Ana Trujillo Avda. de la Constitución 2222
México D.
05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.
05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund
Berguvsvägen 8 Luleå S-958 22 Sweden
WHERE Clause Example
The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:
Example
####### SELECT * FROM Customers
####### WHERE Country='Mexico';
Try it yourself »
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:
Example
####### SELECT * FROM Customers
####### WHERE CustomerID=1;
Try it yourself »
The OR operator displays a record if either the first condition OR the second condition is true.
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCodeCountry
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados
Ana Trujillo Avda. de la Constitución 2222
México D.
05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.
05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund
Berguvsvägen 8 Luleå S-958 22 Sweden
AND Operator Example
The following SQL statement selects all customers from the country "Germany" AND the city "Berlin", in the "Customers" table:
Example
####### SELECT * FROM Customers
####### WHERE Country='Germany'
####### AND City='Berlin';
Try it yourself »
OR Operator Example
The following SQL statement selects all customers from the city "Berlin" OR "München", in the "Customers" table:
Example
####### SELECT * FROM Customers
####### WHERE City='Berlin'
####### OR City='München';
Try it yourself »
Combining AND & OR
You can also combine AND and OR (use parenthesis to form complex expressions).
The following SQL statement selects all customers from the country "Germany" AND the city must be equal to "Berlin" OR "München", in the "Customers" table:
Example
####### SELECT * FROM Customers
####### WHERE Country='Germany'
####### AND (City='Berlin' OR City='München');
Try it yourself »
SQL ORDER BY Keyword
« Previous
Next Chapter »
The ORDER BY keyword is used to sort the result-set.
The SQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set by one or more columns.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.
SQL ORDER BY Syntax
####### SELECT column_name , column_name
####### FROM table_name
####### ORDER BY column_name , column_name ASC|DESC;
Example
####### SELECT * FROM Customers
####### ORDER BY Country DESC;
Try it yourself »
ORDER BY Several Columns Example
The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column:
Example
####### SELECT * FROM Customers
####### ORDER BY Country,CustomerName;
Try it yourself »
SQL INSERT INTO Statement
« Previous
Next Chapter »
The INSERT INTO statement is used to insert new records in a table.
The SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.
SQL INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two forms.
The first form does not specify the column names where the data will be inserted, only their values:
####### INSERT INTO table_name
####### VALUES ( value1 , value2 , value3 ,...);
The second form specifies both the column names and the values to be inserted:
####### INSERT INTO table_name ( column1 , column2 , column3 ,...)
####### VALUES ( value1 , value2 , value3 ,...);
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCodeCountry
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
INSERT INTO Example
Assume we wish to insert a new row in the "Customers" table.
We can use the following SQL statement:
Example
####### INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode,
####### Country)
####### VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
Try it yourself »
CustomerID CustomerName ContactName Address City PostalCodeCountry
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B
Seattle 98128 USA
90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
92 Cardinal null null Stavanger Null Norway
SQL UPDATE Statement
« Previous
Next Chapter »
The UPDATE statement is used to update records in a table.
The SQL UPDATE Statement
The UPDATE statement is used to update existing records in a table.
SQL UPDATE Syntax
####### UPDATE table_name
####### SET column1 = value1 , column2 = value2 ,...
####### WHERE some_column = some_value ;
Notice the WHERE clause in the SQL UPDATE statement! The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCodeCountry
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados
Ana Trujillo Avda. de la Constitución 2222
México D.
05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.
05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund
Berguvsvägen 8 Luleå S-958 22 Sweden
SQL UPDATE Example
Assume we wish to update the customer "Alfreds Futterkiste" with a new contact person and city.
We use the following SQL statement:
Example
####### UPDATE Customers
####### SET ContactName='Alfred Schmidt', City='Hamburg'
####### WHERE CustomerName='Alfreds Futterkiste';
Try it yourself »
The selection from the "Customers" table will now look like this:
CustomerID CustomerName ContactName Address City PostalCodeCountry
« Previous
Next Chapter »
The DELETE statement is used to delete records in a table.
The SQL DELETE Statement
The DELETE statement is used to delete rows in a table.
SQL DELETE Syntax
####### DELETE FROM table_name
####### WHERE some_column = some_value ;
Notice the WHERE clause in the SQL DELETE statement! The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCodeCountry
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados
Ana Trujillo Avda. de la Constitución 2222
México D.
05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.
05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund
Berguvsvägen 8 Luleå S-958 22 Sweden
SQL DELETE Example
Assume we wish to delete the customer "Alfreds Futterkiste" from the "Customers" table.
We use the following SQL statement:
Example
####### DELETE FROM Customers
####### WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';
Try it yourself »
The "Customers" table will now look like this:
CustomerID CustomerName ContactName Address City PostalCodeCountry
2 Ana Trujillo Emparedados y helados
Ana Trujillo Avda. de la Constitución 2222
México D.
05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 231 2 México D.
05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund
Berguvsvägen 8 Luleå S-958 22 Sweden
Delete All Data
SQL- Tutorial asdsa dsadsadsadsadsadsadsadsa dsadsadsa
Course: Statistics (STAT 101)
University: University of Mindanao
- Discover more from:
