SQL Questions and Answers
1. The
INSERT command including the pirate modifiers is used to:
A- Add
rows or columns to a table, as well as replace specific values with a specified
value.
B- Only
to add columns to a table.
C- Only to add rows to a table
D- Only
to add rows or columns to a table
2. Which
SQL command would be used to add a column to a table:
A-
ALTER
TABLE
B- CHANGE
STRUCTRE
C- MODIFY
DATABASE
D- STRUCTRE
3. What
does a Null value represent in a Database?
A- A
negative or false value
B- A
text string full of spaces
C-
Missing
data
D- An
error, such as ‘ illegal value’
4. Which
of the following commands will not an After Trigger to execute:
A. Insert
B.
Alter
C. Update
D. Delete
5. Which
SQL command is used to constrain (limit) the rows input into a query:
A- CONDITION
B- RULE
C- WHEN
D-
WHERE
6. The
_______ SQL command returns rows of data that appear in two similarly structured
tables or queries (i.e, only rows that appear in both tables are returned).
A- INSERT
B- COMMON
C-
OVERLAP
7. Which
SQL command is used to control the order that records are displayed in a query
result:
A. INDEX
BY
B.
ORDER
BY
C. SELECT
BY
D. SORT
BY
8. What
would have to be done to the following query to make it run without error?
SELECT CustomerCity,
Count(CustomerKey)
FROM Customer
A- Nothing
it will run fine as it is.
B- Drop
the column CustomerCity. You cannot have any columns in SELECT with an
Aggregate Functioin
C-
Add
the clause GROUP BY CustomerCity
D- ORDER
BY
9.
Which
SQL command would you use to remove a table and it’s contents from a database?
A)
Drop table
B)
Group
by
C)
Index
by
D)
Sequence
by
10. Given two tables with 1000 rows of
data cross join would produce?
A)
a
resulting join of 100 rows of data
B)
a
resulting join of 1,000 rows of data
C)
a
resulting join of 100,00 rows of data
D)
a resulting join of 1,000,000 rows of
data
11. Which JOIN will create the largest
table?
A)
INNER
B)
RIGHT
C)
LEFT
D)
FULL OUTER
12. What is the difference between a
LEFT JOIN and RIGHT JOIN?
A)
Nothing—they
always give the same result.
B)
A
LEFT JOIN will result in a larger table.
C)
Only the order of the tables.
D)
A
RIGHT JOIN will result in a larger table.
13. Which is the correct sequence for
an SQL statement?
A)
SELECT,
WHERE, GROUP BY, FROM, HAVING, ORDER BY, INNER JOIN
B)
SELECT,
FROM, WHERE, INNERJOIN, GROUP BY, HAVING, ORDER BY.
C)
SELECT,
INNER JOIN, FROM, GROUP BY, ORDER BY, HAVING
D)
SELECT, FROM, INNER JOIN, WHERE, GROUP
BY, HAVING ORDER BY.
14. Which of the following is not true
about SQL?
A)
SQL
can be used to create the entire database.
B)
SQL
has powerful commands to alter the data.
C)
SQL is standardized by the National
Query Association.
D)
SQL
was designed to work with sets of data.
15. Fill in the blank- 2 points each.
Provide the word or phrase that best completes the statement or answers
question. Write your answers on this test paper.
The GROUP BY SQL Command is used to eliminate groups of aggregated
data from a query result.
The Drop command is part of the data definition SQL
sub-language.
In SQL tables are connected with a JOIN statement.
The SQL command used to determine if a value is
contained in a set of values is the IN operator.
The SQL command used to match field values to a
template string or pattern (e.g. to find all names beginning the letters Joh)
is LIKE “Joh%”
The Cartesian product
of two sets is also known in the database world as a __
- Collated sub
query
- Cross join
- Intersection
- Union
A stored query is
called a ___
- Prespective
- Snapshot
- View
- Window
A procedure that
executes when an action query occurs within the database is called a __
- Action
- Alert
- Response
- Trigger
Which of the following
are true of INSERT elements?
- Value match the columns in
sequence
- You must enter
values for every required column
- Both A and B
- None of the above
Which of the following
best describes what the following code does?
DELETE FROM Contact
WHERE Contactkey <
(SELECT MAX (Contactkey)
FROM Contact c
WHERE Contact.LastName
= C.LastName
AND
Contact.FirstName=C.FirstName
AND Contact.Email =
c.Email
AND
Contact.Phone=c.Phone
- It removes all
contact from the contact table
- It removes duplicates
from the contact table
- It removes all unmatched data
- None of the above
What would be the
effect of this UPDATE statement?
UPDATE Customer
SET CustomerLastName =
"Smith"
- It would change one
customer's last name to Smith
- It would have no effect
- It would change every customer's
last name to Smith
- None of the
above.
Which SQL command
would you use to remove a table and its contents from a database?
- DROP TABLE
- GROUP BY
- INDEX BY
- SEQUENCE BY
|
·
|
SQL
Coding Questions:
1- List the Item ID and the List price of Dog
Merchandise items that have a List price greater than the average List price of
all Dog Merchandise. (Use the Sally’s pet Store DB).
SELECT
Merchandise.ItemID, Merchadise.ListPrice, FROM Merchandise INNER JOIN Animal ON
Merchase.ListPrice WHERE category=”dog”;
2- Create a stored procedure name PayUnpaidlnvoices
that pays the unpaid invoices in the InvoiceCopy table those invoices that done
have a paymentDate. Pay the invoice in full (set the paymenttotal equal to the invoicetotal)
and use today’s date in the paymentdate column.(use AP DB) .
CREATE PROCEDURE PayUnpaidInvoices @InvoiceCopy Date
(Today) AS SET paymenttotal=invoicetotal and Date=”Today”;
3- create a view name Top5EmployeesBySales that
returns three columns .EmployeeID, Lastname, and Saleprice summed and renamed
Total, Return only the 5 employees with the largest total of animal sales
only (not merchandise).(use Sally’s pet store DB).
CREATE FUNCTION
Top5employeesBySales (@EmployeeID INTEGER)
RETURNS @Employees TABLE (Lastname VARCHAR (10),
Saleprice VARCHAR (10))
AS
BEGIN
INSERT @Employees
SELECT Lastname, Saleprice
From Invoice
RETURN total=saleprice;
Update saleprice SET
saleprice=”Total”
Return
End;
4- List the Supplier ID, Name, Order Date, and State
of suppliers in Kentucky or California who sold dogs to the pet store. (Use the
Sally’s pet Store DB).
SELECT ID, Name, Order Date
FROM Supplier JOIN AnimalOrderItem INNER JOIN ANIMAL WHERE
CityID=Kentucky||California AND Animal.Name=”Dog”;
5- Write a Delete statement that deletes invoices in
the InvoiceCopy table with a TermsID of 3 for Vendors in the state of CA. (use
DB).
DELETE FROM InvoiceCopy WHERE (TermsID=”3”) AND
(state=”CA”);
6- List the employee firstname, lastname, total of
animal purchases related to the employee, total of Merchandise purchases
related to the employee, and the employee purchases ratio (Merchandise
purchases/Animal Merchandise) for employees where the purchases ratio least. 23
or greater. (Use the Sally’s pet Store DB).
SELECT
Employee.EmployeeID, Employee.firstname, Employee.LastName,
MerchandiseOrder.OrderDate, City.State
FROM City INNER JOIN (Supplier INNER JOIN (Employee INNER JOIN
MerchandiseOrder ON Employee.EmployeeID = MerchandiseOrder.EmployeeID) ON
Supplier.SupplierID = MerchandiseOrder.SupplierID) WHERE ((Merchandise.QuantityOnHand >=23));
FROM City INNER JOIN (Supplier INNER JOIN (Employee INNER JOIN
MerchandiseOrder ON Employee.EmployeeID = MerchandiseOrder.EmployeeID) ON
Supplier.SupplierID = MerchandiseOrder.SupplierID) WHERE ((Merchandise.QuantityOnHand >=23));
EmoticonEmoticon