SQL Tutorial Flashcards
Source: http://www.w3schools.com/sql/default.asp (121 cards)
1
Q
What is SQL?
A
A standard language for accessing databases. It stands for Structured Query Language. It is an ANSI standard however there are different versions of the language.
2
Q
What is ANSI?
A
American National Standards Institute
3
Q
What things do you need to build a website that shows data from a database?
A
- An RDBMS database program
- To use a server-side scripting language like PHP or ASP
- To use SQL to get the data you want
- To use HTML/CSS
4
Q
What is RDBMS?
A
Relational Database Management System.
5
Q
Is SQL case sensitive?
A
No. SELECT is the same as select but normally SQL keywords are written in uppercase.
6
Q
How is a SQL statement ended?
A
With a semi-colon ; Some databases require a semicolon at the end of each SQL statement. It 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.
7
Q
A database contains one or more _____?
A
Tables
8
Q
A table is identified by a _____?
A
Name
9
Q
How would you select all records in a table called “Customers” ?
A
SELECT * FROM Customers;
Pay attention to syntax!
10
Q
What are the 11 “Most Important SQL Commands?”
A
SELECT
UPDATE
DELETE
INSERT INTO
CREATE DATABASE
ALTER DATABASE
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
DROP INDEX
11
Q
What does SELECT do?
A
extracts data from a database
12
Q
What does UPDATE do?
A
updates data in a database
13
Q
What does DELETE do?
A
deletes data (rows) from a database
14
Q
What does INSERT INTO do?
A
inserts new records into a database
15
Q
What does CREATE DATABASE do?
A
creates a new database
16
Q
What does ALTER DATABASE do?
A
modifies a database
17
Q
What does CREATE TABLE do?
A
creates a new table
18
Q
What does ALTER TABLE do?
A
modifies a table
19
Q
What does DROP TABLE do?
A
deletes a table
20
Q
What does CREATE INDEX do?
A
creates an index (search key)
21
Q
What does DROP INDEX do?
A
deletes an index
22
Q
What statement creates a new table?
A
CREATE TABLE
23
Q
What statement deletes a table?
A
DROP TABLE
24
Q
What statement creates a search key?
A
CREATE INDEX
25
What statement deletes data from a database?
DELETE
26
What statement extracts data from a database?
SELECT
27
What statement updates data in a database?
UPDATE
28
What statement inserts new data?
INSERT INTO
29
What statement deletes an index?
DROP INDEX
30
What statement creates a database?
CREATE DATABASE
31
What statement modifies a table?
ALTER TABLE
32
What statement modifies a database?
ALTER DATABASE
33
What happens to the data from a SELECT statement?
The results are stored in a result table, called the result-set.
34
What is the complete syntax for a SELECT statement?
SELECT column\_name, column\_name
FROM table\_name;
and
SELECT \* FROM table\_name;
35
What is the statement to select the CustomerName and City columns from the Customers table?
SELECT CustomerName, City
FROM Customers;
36
What does
SELECT \* FROM Customers;
do?
It selects all columns from the Customers table.
37
How do you navigate within a result set?
Most database systems allow navigation in the result-set with programming functions, like: Move-To-First-Record, etc. Programming functions like these are not part of this SQL tutorial. To learn about accessing data with function calls they have ASP and PHP tutorials.
38
How do you return only different values? What example was used on the "Customer" table?
SELECT DISTINCT
SELECT DISTINCT City FROM Customers;
It returns a list of cities in the Customer table, but only once, even if several customers are in the same city.
39
How do you filter records? What is the syntax?
WHERE
SELECT column\_name, column\_name
FROM table\_name
WHERE column\_name operator value;
40
How would you find customers in the database who are in Mexico? Remember all the syntax.
SELECT \* FROM Customers
WHERE Country='Mexico';
41
How are text fields and numeric fields handled differently?
SQL requires single quotes around text values (e.g. Country='Mexico'). Most databases will allow double quotes.
Numeric field values should not be enclosed in quotes (e.g. CustomerID=1)
42
What 9 operators can be used in the WHERE clause?
43
If you have more than one condition what are the available operators and what do they mean?
The AND operator displays a record if both the first condition AND the second condition are true.
The OR operator displays a record if either the first condition OR the second condition is true.
44
How would you select customers from only the Berlin in Germany? Remember syntax.
SELECT \* FROM Customers
WHERE Country='Germany' AND City='Berlin';
45
How would you select customers that are either in Berlin or Munchen?
SELECT \* FROM Customers
WHERE City='Berlin' OR City='Munchen';
46
How would you select customers that are in Germany and either in Berlin or Munchen? (No Berlins or Munchens outside of Germany)
SELECT \* FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='Munchen');
47
What keyword sorts the result-set?
ORDER BY
48
What is the default order?
ORDER By sorts records in ascending order by default.
49
How do you sort in descending order?
DESC keyword
50
ORDER BY syntax
SELECT *column\_name*, *column\_name*
FROM *table\_name*
ORDER BY *column\_name* ASC|DESC, *column\_name* ASC|DESC;
There need not be more than one column to sort by.
51
How would you order Customers by Country?
SELECT \* FROM Customers
ORDER BY Country;
52
How would you order Customers by Country, descending?
SELECT \* FROM Customers
ORDER BY Country DESC;
53
How would you order Customers by Country and CustomerName?
SELECT \* FROM Customers
ORDER BY Country, CustomerName;
54
How would you order Customers by Country ascending and CustomerName descending?
SELECT \* FROM Customers
ORDER BY Country ASC, CustomerName DESC;
55
What is the first form of INSERT INTO?
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,...);
56
What is the second form of INSERT INTO?
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,...);
This is used to insert data only in specific columns.
57
How would you add
CustomerName Cardinal,
City Stavanger
and Country Norway as a new record?
## Footnote
*Remember syntax.*
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
58
If you insert values into a new record but only some of the columns, what happens to the other columns you didn't add anything to?
They are null.
59
What is the syntax for an UPDATE?
UPDATE table\_name
SET column1=value1, column2=value2,...
WHERE some\_column=some\_value;
60
What is a big gotcha in UPDATE and what happens?
If you forget the WHERE clause which specifies which record should be updated, all records will be updated (all their data will be overwritten with this data).
61
To reiterate
What are the three parts of an UPDATE?
UPDATE SET WHERE
Don't forget the WHERE part!
62
How would you update the
CustomerName is Alfreds Futterkiste
with a new ContactName and City (Alfred Schmidt and Hamburg)?
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';
63
What is the syntax for DELETE?
DELETE FROM table\_name
WHERE some\_column=some\_value;
This deletes rows in a table.
64
How do you delete the customer "Alfreds Futterkiste" from the "Customers" table?
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste';
65
How would you delete all data from a table without deleting the table?
DELETE FROM table\_name;
*or*
DELETE \* FROM table\_name;
*You cannot undo this!*
66
An SQL Injection can ....
destroy your database. It is a danger in using user input in SQL statements. Injected SQL commands can alter SQL statements and compromise the security of a web application. Malicious users can inject SQL commands into a SQL statement, via web page input.
67
What does this do?
txtUserId = getRequestString("UserId");
txtSQL = "SELECT \* FROM Users WHERE UserId = " + txtUserId;
This example is server code that creates a select statement by adding a variable (txtUserID) to a select string. The variable is fetched from user input (Request) to the page.
68
What happens if a hacker inputs
105 or 1=1
into an input field for UserID ?
The server code result in this example would yield
SELECT \* FROM Users
WHERE UserId = 105 or 1=1.
Since 1=1 is always true it will return all rows (all data from all rows since it's \*). If this was a table with UserID, Name and Passwords it would return all that data. (Remember or is the same as OR since most dbs are not case sensitive and we just capitalize keywords as a best practice).
69
What statement using = is always true and can be used to create valid SQL that returns all data?
""="" is always true. (2 double quotes, =, 2 double quotes)
So if this was put into a username and password field it would return all rows since WHERE ''"="" is always true.
70
How can batched SQL statements be exploited?
Most databases support batched SQL statements, separated by a semicolon. An example would be if someone inputted this into the User Id field on the website
105; DROP TABLE Suppliers
It could create valid code like this
SELECT \* FROM Users WHERE UserId = 105; DROP TABLE Suppliers
This would return UserID 105 and then proceed to "next statement" which would delete the table Suppliers. (Presumably it's OK that there is no semi-colon at the end since there's not another statement to separate this from)
71
What are blacklists and why are they not a great idea?
Some web developers create a blacklist of words or characters to search for in SQL input to prevent SQL injection attacks. However many of these words (like delete or drop) and characters (like semicolons and quotation marks) are used in common language and have legitimate uses and should be allowed in many types of input. Also, it should be legal to input an SQL statement in a database field.
72
What is the only proven way to protect a website from SQL injection attacks?
SQL parameters
They are values added to an SQL query at execution time, in a controlled manner. The SQL engine checks each parameter to ensure that it is correct for its column and are treated literally, and not as part of the SQL to be executed. See tutorial for examples.
73
How do you specify the number of records to return and why do you need to? How does each database do this?
SELECT TOP
This can be useful on large tables with thousands of records because returning a large number of records can impact performance.
**SQL Server/MS Access Syntax**
SELECT TOP number | percent column\_name(s)
FROM table\_name;
Equivalents
**MySQL Syntax** (note, it does not use the word TOP)
SELECT column\_name(s)
FROM table\_name LIMIT number;
**Oracle Syntax**
SELECT column\_name(s)
FROM table\_name WHERE ROWNUM \<= number;
74
Give 2 examples in SQL Server syntax to
1) select the first 2 records
* and then*
2) the first 50%.
SELECT TOP 2 \* FROM Customers;
SELECT TOP 50 PERCENT \* FROM Customers;
75
How do you search for a specified pattern in a column?
LIKE
SELECT column\_name(s)
FROM table\_name
WHERE column\_name LIKE pattern;
76
How do you select all customers in a City starting with the letter "s"?
's%'
SELECT \* FROM Customers
WHERE City LIKE 's%';
77
How do you select all customers in a City ending with the letter "s"?
'%s' SELECT \* FROM Customers
WHERE City LIKE '%s';
78
How do you select all customers in a City containing "land"?
'%land%'
SELECT \* FROM Customers
WHERE Country LIKE '%land%';
79
How do you select all customers in a City that does not contain "land"?
NOT LIKE
SELECT \* FROM Customers
WHERE Country NOT LIKE '%land%';
80
When are wildcards used in SQL?
With the LIKE operator, to define a pattern to search for data within a table.
81
What wildcard substitutes for a single character?
\_ (underscore)
82
What wildcard substitutes for zero or more characters?
%
83
How do you search for a set and range of characters to match?
[charlist]
84
How do you match only a character NOT specified?
[^charlist] or [!charlist]
Matches only a character NOT specified in the charlist.
85
Select all customers with a City starting with "ber".
SELECT \* FROM Customers
WHERE City LIKE 'ber%';
86
Select all customers with a city containing "es".
SELECT \* FROM Customers
WHERE City LIKE '%es%';
87
Select all customers with a city starting with any letter followed by "erlin".
SELECT \* FROM Customers
WHERE City LIKE '\_erlin';
88
Select all customers in a city starting with L, then any character, then n, then any character, then on.
SELECT \* FROM Customers
WHERE City LIKE 'L\_n\_on';
89
Customers with a city starting with a b, s or p
SELECT \* FROM Customers
WHERE City LIKE '[bsp]%';
90
Customers with a city starting with a, b, c or d
SELECT \* FROM Customers
WHERE City LIKE '[a-d]%';
*(this is using range)*
91
Customers with a city NOT starting with a b, s or p - 2 ways
SELECT \* FROM Customers
WHERE City LIKE '[!bsp]%';
or
SELECT \* FROM Customers
WHERE City NOT LIKE '[bsp]%';
92
What does the IN operator do and what is the syntax?
The IN operator allows you to specify multiple values in a WHERE clause.
SELECT column\_name(s)
FROM table\_name
WHERE column\_name IN (value1,value2,...);
93
select all customers with a City of "Paris" or "London" without using OR
SELECT \* FROM Customers
WHERE City IN ('Paris','London');
94
What is BETWEEN and what is the syntax?
The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.
SELECT column\_name(s)
FROM table\_name
WHERE column\_name BETWEEN value1 AND value2;
95
select all products with
1) a price between 10 and 20
2) a price not between 10 and 20
SELECT \* FROM Products
WHERE Price BETWEEN 10 AND 20;
SELECT \* FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
96
select all products with a price between 10 and 20, unless they are in CategoryID of 1,2, or 3, without using a [charlist] or CategoryID \<\>.
SELECT \* FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);
97
Select all products with a ProductName beginning with any of the letter between C and M without using LIKE.
SELECT \* FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';
*syntax!*
Yes, I know it's weird that this works for the first letter but that's how text BETWEEN works.
98
Sselect all products with a ProductName beginning with any of the letter not between C and M
SELECT \* FROM Products
WHERE ProductName NOT BETWEEN 'C' AND 'M';
99
Select all orders with an OrderDate between 04-July-1996 and 09-July-1996
SELECT \* FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;
100
How can databases vary in how they evaluate BETWEEN?
**Notice that the BETWEEN operator can produce different result in different databases!**
In some databases, BETWEEN selects fields that are between and *excluding* the test values.
In other databases, BETWEEN selects fields that are between and *including* the test values.
And in other databases, BETWEEN selects fields between the test values, ***including** the first test value* and ***excluding** the last test value*.
Therefore: Check how your database treats the BETWEEN operator!
101
What are aliases and what is the syntax?
SQL aliases are used to give a database table, or a column in a table, a temporary name. Basically aliases are created to make column names more readable.
SQL Alias Syntax for Columns
SELECT column\_name AS alias\_name
FROM table\_name;
SQL Alias Syntax for Tables
SELECT column\_name(s)
FROM table\_name AS alias\_name;
102
Select from the following columns and specifie two aliases, one for the CustomerName (Customer) column and one for the ContactName (Contact Person) column from the Customers table.
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
Tip: It requires double quotation marks or square brackets if the column name contains spaces:
103
Select CustomerName and also *combine* four columns (Address, City, PostalCode, and Country) and create an alias named "Address" from the Customer table.
SELECT CustomerName, Address+', '+City+', '+PostalCode+', '+Country AS Address
FROM Customers;
or for MySQL
SELECT CustomerName, CONCAT(Address,', ',City,', ',PostalCode,', ',Country) AS Address
FROM Customers;
104
Select all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we have used aliases to make the SQL shorter)
columns in Orders - OrderID, OrderDate
column in Customers - CustomerName
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;
Done without aliases
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName="Around the Horn" AND Customers.CustomerID=Orders.CustomerID;
105
Aliases can be useful when...(4 points)
* There are more than one table involved in a query
* Functions are used in the query
* Column names are big or not very readable
* Two or more columns are combined together
106
What are joins and what is the syntax for the most common type?
SQL joins are used to combine rows from two or more tables.
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.
SELECT column\_name(s)
FROM table1
INNER JOIN table2
ON table1.column\_name=table2.column\_name;
107
What is an INNER JOIN?
Returns all rows when there is at least one match in BOTH tables.
Note: INNER JOIN is the same as JOIN
108
What is a LEFT JOIN?
Return all rows from the left table, and the matched rows from the right table
109
What is a RIGHT JOIN?
Return all rows from the right table, and the matched rows from the left table
110
What is a FULL JOIN?
Return all rows when there is a match in ONE of the tables
111
What does the inner join look like diagrammed?
112
Return all customers with orders (their names and their order id) from the Customers and Orders tables and sort it by name.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
113
Explain LEFT JOIN and syntax.
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. In some databases LEFT JOIN is called LEFT OUTER JOIN.
SELECT column\_name(s)
FROM table1
LEFT JOIN table2
ON table1.column\_name=table2.column\_name;
or
SELECT column\_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column\_name=table2.column\_name;
114
What does a LEFT JOIN look like in the diagram?
115
How would you use LEFT JOIN to return all customers and any orders they might have?
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders).
116
What is RIGHT JOIN and what is the syntax?
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
SELECT column\_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column\_name=table2.column\_name;
or
SELECT column\_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column\_name=table2.column\_name;
117
What does a RIGHT JOIN or RIGHT OUTER JOIN look like?
118
Return all employees, and any orders they have placed using a RIGHT JOIN.
SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY Orders.OrderID;
The RIGHT JOIN keyword returns all the rows from the right table (Employees), even if there are no matches in the left table (Orders).
119
What is a FULL OUTER JOIN and what is the syntax?
What does it look like?
The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).
The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
SELECT column\_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column\_name=table2.column\_name;
120
Select all customers, and all orders using a FULL OUTER JOIN.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well. Result may look like this...
CustomerName OrderID
Alfreds Futterkiste
Ana Trujillo Emparedados y helados 10308
Antonio Moreno Taquería 10365
10382
10351
121
Start here http://www.w3schools.com/sql/sql\_union.asp
answer answer
