SQL Tutorial

operators in the WHERE clause 1. not equal 2. between an inclusive range 3. search for a pattern 4. specify multiple possible values for a column 5. filter records based on more than one condition

1. <>, != 2. BETWEEN 3. LIKE 4. IN 5. AND, OR

delete all data

DELETE * FROM Customers; DELETE FROM Customers;

delete the customer "Alfreds Futterkiste" from the "Customers" table

DELETE FROM Customers WHERE CustomerName="Alfreds Futterkiste";

insert a new row, but only insert data in the "CustomerName", "City", and "Country" columns

INSERT INTO Customers (CustomerName, City, Country) VALUES(......);

insert a new row in the "Customers" table

INSERT INTO Customers VALUES(......);

select all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column

SELECT * FROM Customers ORDER BY Country, CustomerName DESC;

select all customers from the "Customers" table, sorted by the "Country" column

SELECT * FROM Customers ORDER BY Country;

select all customers from the "Customers" table, sorted DESCENDING by the "Country" column

SELECT * FROM Customers OREDER BY Country DESC;

select all customers from the city "Berlin" OR "München", in the "Customers" table

SELECT * FROM Customers WHERE City="Berlin" OR City="Munchen";

select all customers from the country "Germany" AND the city must be equal to "Berlin" OR "München", in the "Customers" table

SELECT * FROM Customers WHERE Country="Germany" AND (City="Berline" OR City="Munchen");

select all customers from the country "Germany" AND the city "Berlin", in the "Customers" table

SELECT * FROM Customers WHERE Country="Germany" AND City="Berlin";

select all the customers from the country "Mexico", in the "Customers" table

SELECT * FROM Customers WHERE Country="Mexico";

select all from the "Customers" table

SELECT * FROM Customers;

select the "CustomerName" and "City" columns from the "Customers" table

SELECT CustomerName, City FROM Customers;

select only the distinct values from the "City" columns from the "Customers" table

SELECT DISTINCT City FROM Customers;

update the customer "Alfreds Futterkiste" with a new contact person and city

UPDATE Customers SET ContactName="", City="" WHERE CustomerName="Alfreds Futterkiste";

create a select statement by adding a variable (txtUserId) to a select string. The variable is fetched from the user input (Request) to the page

txtUserId = getRequestString("UserId"); txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;

UPDATE

updates data in a database

protect a web site from SQL injection attacks

use SQL parameters (values that are added to an SQL query at execution time, in a controlled manner)

CREATE DATABASE

creates a new database

CREATE INDEX

creates an index (search key)

DROP TABLE

deletes a table

DROP INDEX

deletes an index

DELETE

deletes data from a database

ALTER DATABASE

modifies a database

SELECT DISTINCT

return only distinct (different) values

ORDER BY

sort the result-set in ascending order by default

INSERT INTO

inserts new data into a database

RDBMS (2)

1. Relational Database Management System 2. the basis for SQL 3. the data is stored in tables

SQL injection (2)

1. a technique where malicious users can inject SQL commands into an SQL statement, via web page input 2. can alter SQL statement and compromise the security of a web application

SELECT ()

1. extracts data from a database 2. the result is stored in a result table

SQL

Structured Query Language

WHERE

filter records