SQL - Data Management - Applications - Study to PASS the test ... 2 Flashcards
The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.
The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
ex :
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
ex2 :
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
ex3 :
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
GROUP BY
The INSERT INTO SELECT statement copies data from one table and inserts it into another table.
The INSERT INTO SELECT statement requires that the data types in source and target tables matches.
Note: The existing records in the target table are unaffected.
ex (copy all columns from one table to another) :
INSERT INTO table2 SELECT * FROM table1 WHERE condition;
ex2 (copy only some columns from one table into another table) :
INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;
ex3 :
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
e4 (this statement copies “Suppliers” into “Customers” - fills all columns) :
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;
INSERT INTO SELECT
The SELECT INTO statement copies data from one table into a new table.
ex (copy all columns into a new table) :
SELECT *
INTO newtable [IN externalDataBase]
FROM oldtable
WHERE condition;
ex2 (copy only some columns into a new table) :
SELECT column1, column2, column3, …
INTO newtable [IN externalDataBase]
FROM oldtable
WHERE condition;
SELECT INTO
ex3 (creates a backup copy of Customers table) :
SELECT * INTO CustomersBackup2017
FROM Customers;
ex4 (uses IN clause to copy table into a new table in another database) :
SELECT * INTO CustomersBackup2017 IN ‘Backup.mdb’
FROM Customers;
ex5 (copies data from more than one table into a new table) :
SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ex6 (SELECT INTO can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data) :
SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;
SELECT INTO (2nd Set of Examples)
DCL commands are used to enforce database security in a multiple user database environment. Two types of DCL commands are GRANT and REVOKE. Only Database Administrator’s or owner’s of the database object can provide/remove privileges on a database object.
ex :
GRANT privilege_name
ON object_name
TO {user_name | PUBLIC | role_name}
[WITH GRANT OPTION];
privilege_name is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT.
object_name is the name of an database object like TABLE, VIEW, STORED PROC and SEQUENCE.
user_name is the name of the user to whom an access right is being granted.
PUBLIC is used to grant access rights to all users.
ROLES are a set of privileges grouped together.
WITH GRANT OPTION - allows a user to grant access rights to other users.
The REVOKE command removes user access rights or privileges to the database objects.
ex :
REVOKE privilege_name
ON object_name
FROM {user_name | PUBLIC | role_name}
1) System privileges - This allows the user to CREATE, ALTER, or DROP database objects.
2) Object privileges - This allows the user to EXECUTE, SELECT, INSERT, UPDATE, or DELETE data from database objects to which the privileges apply.
GRANT / REVOKE
CREATE TABLE … LIKE creates a new table as an empty copy of the original one. It copies the original table structure exactly, so that each column is preserved with all of its attributes. The index structure is copied as well. However, the new table is empty, so to populate it a second statement is needed (such as INSERT INTO … SELECT). Also, CREATE TABLE … LIKE cannot create a new table from a subset of the original table’s columns, and it cannot use columns from any other table but the original one.
To use CREATE TABLE … LIKE for creating an empty copy of an existing table, write a statement like this:
CREATE TABLE new_tbl_name LIKE tbl_name;
CREATE TABLE … SELECT creates a new table from the result of an arbitrary SELECT statement. By default, this statement does not copy all column attributes such as AUTO_INCREMENT. Nor does creating a table by selecting data into it automatically copy any indexes from the original table, because result sets are not themselves indexed. On the other hand, CREATE TABLE … SELECT can both create and populate the new table in a single statement. It also can create a new table using a subset of the original table and include columns from other tables or columns created as the result of expressions.
CREATE TABLE … SELECT also can create new tables that don’t contain exactly the same set of columns in an existing table. You can use it to cause a new table to spring into existence on the fly to hold the result of an arbitrary SELECT query. This makes it exceptionally easy to create a table fully populated with the data in which you’re interested, ready to be used in further statements. However, the new table can contain strange column names if you’re not careful. When you create a table by selecting data into it, the column names are taken from the columns that you are selecting.
To create an empty copy of a table and then populate it from the original table, use CREATE TABLE … LIKE followed by INSERT INTO … SELECT:
CREATE TABLE new_tbl_name LIKE tbl_name;
INSERT INTO new_tbl_name SELECT * FROM tbl_name;
CREATE TABLE etc …
The UPDATE statement is used to modify existing rows in a table.
ex :
UPDATE table_name
SET column_name1 = value1
column2 = value2, …
WHERE condition;
ex2 :
UPDATE employee
SET location = ‘LA’
WHERE id = 101;
** MIN () / MAX() = get min and max value(s) from a column. **
UPDATE
A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server.
HAVING : restricts the selection of grouped rows based on a condition.
ORDER BY : orders the selected rows based on one or more attributes … default is in ascending order.
Cascade Delete Rule Etc …
EXISTS : checks if a subquery returns any rows.
COUNT : returns the number of rows with non-null values for a given column.
SUM : returns the sum of all values for a given column.
AVG : returns the average for all values for a given column.
EXISTS / COUNT / SUM / AVG
Transaction safe tables (CREATE TABLE) have support for data integrity, implementation of database transactions, transaction logs, and improved backup and recovery options.
** By default all indexes produce results listed in ascending order **
*** ROLLBACK undo’s any changes since the last COMMIT command and brings all data back to the values that existed before the changes were made.
TABLES / Indexes
