SQL - Data Management - Applications - Study to PASS the test ... 1

Question 1

SELECT DISTINCT : is used to return only distinct (different) values.

ex :

SELECT DISTINCT column1, column2 …
FROM table_name;

Answer 1

SELECT DISTINCT

Question 2

Count() : returns number of rows that matches a specified criterion.

ex :

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

real ex :

SELECT COUNT(ProductID)
FROM Products;

Answer 2

COUNT()

Question 3

AVG() : returns the average value of a numeric column.

ex :

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Answer 3

AVG()

Question 4

SUM() : returns total sum of a numeric column.

ex :

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Answer 4

SUM()

Question 5

A VIEW : virtual table has rows and columns like a real table, the fields in a view are fields from one or more real tables in the database.

ex :

CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;

ex2 :

CREATE VIEW Brazil_Customers AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = ‘Brazil’;

ex3 :

CREATE VIEW Products_Above_Average_Price AS
SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

*** DROP VIEW view_name; — deletes a view

Answer 5

CREATE VIEW

Question 6

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

ex :

CREATE INDEX index_name
ON table_name (column1, column2, ...);

ex2 (create unique index) :

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

ex3 :

DROP INDEX index_name ON table_name;

Answer 6

CREATE INDEX / DROP INDEX

Question 7

The DELETE statement is used to delete existing records in a table. The WHERE statement specifies the records (rows) to be deleted.

** records refer to rows, and fields refer to columns **

Answer 7

DELETE / WHERE info …

Question 8

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

ex :

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table.column_name = table2.column_name;

In below ex2 : Customers and Orders are the tables, CustomerName and OrderID are attributes.

ex2 :

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

Answer 8

LEFT JOIN

Question 9

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

ex :

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table.column_name = table.column_name;

ex2 :

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

Answer 9

RIGHT JOIN

Question 10

The FULL JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

ex :

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

ex2 :

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Answer 10

FULL JOIN