Advance SQL Statements Flashcards
(51 cards)
What is the SELECT TOP clause used for?
The SELECT TOP clause is used to specify the number of records to return.
The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.
Note: Not all database systems support the SELECT TOP clause.
What is the syntax for the SELECT TOP clause SQL Server / MS Access statement?
SELECT TOP number|percent column_name(s)
FROM table_name;
What is the syntax for the SELECT TOP clause MySQL statement?
SELECT column_name(s)
FROM table_name
LIMIT number;
Ex.
SELECT *
FROM Persons
LIMIT 5;
What is the syntax for the SELECT TOP clause Oracle statement?
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <=5;
What is the SELECT TOP clause statement to select the first top 2 records?
SELECT TOP 2 * FROM Customers;
What is the SELECT TOP clause statement to select percent?
SELECT TOP 50 PERCENT * FROM Customers;
What is the LIKE operator used for?
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
What is the syntax for the LIKE operator statement?
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
Ex.
SELECT * FROM Customers
WHERE City LIKE ‘s%’;
What is the statement for the LIKE operator when letters are missing?
SELECT * FROM Customers
WHERE City LIKE ‘%s’;
This statement selects all customers with a City ending with the letter “s”:
SELECT * FROM Customers
WHERE Country LIKE ‘%land%’;
This statement selects all customers with a Country containing the pattern “land”:
What is the statement for the LIKE operator using the NOT keyword?
Ex.
SELECT * FROM Customers
WHERE Country NOT LIKE ‘%land%’;
Using the NOT keyword allows you to select records that does NOT match the pattern.
This statement selects all customers with a Country NOT containing the pattern “land”:
What is the purpose of the IN operator?
The IN operator allows you to specify multiple values in a WHERE clause.
What is the syntax for the IN operator statement?
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,…);
Ex.
SELECT * FROM Customers
WHERE City IN (‘Paris’,’London’);
This statement selects all customers with a City of “Paris” or “London”:
What is the BETWEEN operator used for?
The BETWEEN operator is used to select values within a range.
What is the syntax for the BETWEEN operator statement?
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Ex.
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
This statement selects all products with a price BETWEEN 10 and 20:
What is the statement for the NOT BETWEEN operator?
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
This statement displays the products outside the range of the previous example, use NOT BETWEEN:
What is the statement for the BETWEEN operator with IN?
SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);
This statement selects all products with a price BETWEEN 10 and 20, but products with a CategoryID of 1,2, or 3 should not be displayed:
What is the statement for the BETWEEN operator with text value?
SELECT * FROM Products
WHERE ProductName BETWEEN ‘C’ AND ‘M’;
This statement selects all products with a ProductName beginning with any of the letter BETWEEN ‘C’ and ‘M’:
What is the statement for the NOT BETWEEN operator with text value?
SELECT * FROM Products
WHERE ProductName NOT BETWEEN ‘C’ AND ‘M’;
This statement selects all products with a ProductName beginning with any of the letter NOT BETWEEN ‘C’ and ‘M’:
What is the statement for the BETWEEN operator with date value?
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;
This statement selects all orders with an OrderDate BETWEEN ‘04-July-1996’ and ‘09-July-1996’:
What are JOIN used for?
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
What is the most common type of JOIN?
SQL INNER JOIN (simple join). It return all rows from multiple tables as long as there is a match between the columns in both tables.
What is the syntax for the INNER JOIN keyword statement?
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
Ex.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
This statement will return all customers with orders:
Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in the “Customers” table that do not have matches in “Orders”, these customers will NOT be listed.
PS! INNER JOIN is the same as JOIN.
What is the purpose of the LEFT JOIN?
To 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.
What is the syntax for the LEFT JOIN keyword statement?
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;
Ex.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
This statement will return all customers, and any orders they might have:
Note: The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders).
PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.
