Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

16 cards

()

Section 1

Preview this deck

SQL Syntax

Front

1 / 16

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

7 years ago

Date created

Mar 1, 2020

Cards (16)

Section 1

(16 cards)

SQL Syntax

Front

SELECT * FROM Customers;

Back

SQL Inner Join

Front

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables. --- The following SQL statement will return all customers with orders: SELECT Customers.CustomerName, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

Back

SQL Join

Front

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. Notice that the "CustomerID" column in the "Orders" table refers to the customer in the "Customers" table. The relationship between the two tables above is the "CustomerID" column. Then, if we run the following SQL statement (that contains an INNER JOIN): SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Back

SQL Left Join

Front

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. --- The following SQL statement will 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).

Back

SQL IN

Front

The IN operator allows you to specify multiple values in a WHERE clause - SELECT * FROM Customers WHERE City IN ('Paris','London');

Back

SQL Full Join

Front

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. -- The following SQL statement selects all customers, and all orders: 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.

Back

SQL AND & OR

Front

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 SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');

Back

SQL Right Join

Front

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. -- The following SQL statement will return all employees, and any orders they have placed: SELECT Orders.OrderID, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID ORDER BY Orders.OrderID;

Back

SQL Select Distinct

Front

SELECT DISTINCT City FROM Customers;

Back

SQL Select

Front

The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set. SELECT column_name,column_name FROM table_name;

Back

SQL Alias

Front

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. The following SQL statement selects 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): 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;

Back

SQL LIKE

Front

The LIKE operator is used to search for a specified pattern in a column. SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern; ---- SELECT * FROM Customers WHERE City LIKE '%s'; The following SQL statement selects all customers with a City ending with the letter "s": ----- SELECT * FROM Customers WHERE Country NOT LIKE '%land%'; ---

Back

SQL Where Clause

Front

The WHERE clause is used to extract only those records that fulfill a specified criterion. SELECT column_name,column_name FROM table_name WHERE column_name operator value; --- SELECT * FROM Customers WHERE Country='Mexico'; Operators: =,>,<,<>(not equal), >=, <=, BETWEEN, LIKE, IN

Back

SQL Between

Front

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates. -- The following SQL 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: SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3); --- The following SQL statement selects 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'; --- The following SQL statement selects 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#;

Back

SQL Order By

Front

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. SELECT column_name,column_name FROM table_name ORDER BY column_name,column_name ASC|DESC; -- SELECT * FROM Customers ORDER BY Country,CustomerName;

Back

SQL Wildcard Characters

Front

In SQL, wildcard characters are used with the SQL LIKE operator. SQL wildcards are used to search for data within a table. Wildcard: %, _, [charlist], [^charlist], [!charlist] -- The following SQL statement selects all customers with a City containing the pattern "es": SELECT * FROM Customers WHERE City LIKE '%es%'; ---- The following SQL statement selects all customers with a City starting with "L", followed by any character, followed by "n", followed by any character, followed by "on": SELECT * FROM Customers WHERE City LIKE 'L_n_on'; ---- The following SQL statement selects all customers with a City NOT starting with "b", "s", or "p": SELECT * FROM Customers WHERE City LIKE '[!bsp]%'; -----

Back