1. Your company uses 2 tables to store product information named LocalProducs an
ID: 3594589 • Letter: 1
Question
1. Your company uses 2 tables to store product information named LocalProducs and InternationalProducts. You also use a table named Orders that stores open order information. You must retrieve the ID number and the name of any products that are in the Orders table and either the LocalProducts or the InternationalProducts table.
a. SELECT ID, Name
FROM Orders
UNION
SELECT ID, Name
FROM LocalProducts
INTERSECT
SELECT ID, Name
FROM InternationalProducts;
b. SELECT ID, Name
FROM Orders
INTERSECT
(SELECT ID, Name
FROM LocalProducts
UNION
SELECT ID, Name
FROM InternationalProducts);
c. (SELECT ID, Name
FROM Orders
INTERSECT
SELECT ID, Name
FROM LocalProducts)
UNION
SELECT ID, Name
FROM InternationalProducts;
d. SELECT ID, Name
FROM Orders
INTERSECT
SELECT ID, Name
FROM LocalProducts
UNION
SELECT ID, Name
FROM InternationalProducts;
2. Your company uses the following 2 tables to track users who are active on your company’s forums:
WeeklyActive – contains the names of all users and how many posts were made in the past week on your company’s forums. The default value is 1.
ActiveUsers – tracks how many weeks in a row a user has posted at least one on the forums.
You must create a MERGE statement to be run once a week that does the following actions:
Add a user to the ActiveUsers table if that user appears in the WeeklyActive table and is not already in the ActiveUsers table.
Add 1 to the WeeksActive column on the ActiveUsers table for users that appear in the WeeklyActive table and are already in the ActiveUsers table.
Remove users from the ActiveUsers table if they do not appear in the WeeklyActive table.
(see next page for choices)
Which statement would you use?
a. MERGE INTO ActiveUsers
USING WeeklyActive
ON ActiveUsers.Name = WeeklyActive.Name
WHEN MATCHED
THEN UPDATE SET WeeksActive = WeeksActive +1
WHEN NOT MATCHED
THEN INSERT(Name) VALUES(WeeklyActive.Name)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
b. MERGE INTO ActiveUsers
USING WeeklyActive
ON ActiveUsers.Name = WeeklyActive.Name
WHEN MATCHED
THEN UPDATE SET WeeksActive = WeeksActive +1
WHEN NOT MATCHED
THEN DELETE
WHEN NOT MATCHED BY SOURCE
THEN INSERT(Name) VALUES(WeeklyActive.Name);
c. MERGE INTO WeeklyUsers
USING ActiveUsers
ON ActiveUsers.Name = WeeklyActive.Name
WHEN MATCHED
THEN UPDATE SET WeeksActive = WeeksActive +1
WHEN NOT MATCHED
THEN INSERT(Name) VALUES(WeeklyActive.Name)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
d. MERGE INTO WeeklyUsers
USING ActiveUsers
ON ActiveUsers.Name = WeeklyActive.Name
WHEN MATCHED
THEN UPDATE SET WeeksActive = WeeksActive +1
WHEN NOT MATCHED
THEN DELETE
WHEN NOT MATCHED BY SOURCE
THEN INSERT(Name) VALUES(WeeklyActive.Name);
3. What are the required parts of the SELECT statement?
a. SELECT
b. SELECT, FROM
c. SELECT, FROM, WHERE
d. SELECT, FROM, JOIN
4. Which statements are part of DML?
a. SELECT, INSERT, MODIFY
b. SELECT, INSERT, REMOVE
c. INSERT, MODIFY, DELETE
d. INSERT, UPDATE, DELETE
5. The type of join that includes all rows of from 1 table and the matching rows from the other table is:
a. INNER
b. FULL
c. OUTER
d. SELF
6. If your result set looked like the following, what clause did you use in your SQL statement to get the 0 and 1s?
Product Product
CategoryID SubcategoryID Average
NULL 1 33 0 31.3233
NULL 1 34 0 25.00
NULL 1 35 0 125.00
1 0 NULL 1 1586.737
2 0 NULL 1 469.8602
3 0 NULL 1 50.9914
a. HAVING
b. GROUPING
c. GROUPING SETS
d. Either A or B
e. Either B or C
7. If InvoiceTotal contains a value of 250.00, what will the Solution column contain when this code is executed?
CASE
WHEN InvoiceTotal > 500
THEN InvoiceTotal - ROUND(InvoiceTotal * .20, 2)
WHEN InvoiceTotal >= 250
THEN InvoiceTotal - ROUND(InvoiceTotal * .10, 2)
ELSE
0
END AS Solution
a.
200.00
c.
25
b.
225.00
d.
0
8. If you had the following WHERE clause, what word would be returned?
WHERE Word LIKE ‘_TE_’
a. ESTEEM
b. WATER
c. ITEM
d. ANTE
9. What is an identity column?
a. Provides the default for a column
b. Column value that is automatically incremented for each row
c. Provides uniqueness to a column
d. Both A and B
e. Both B and C
10. What number sets would be returned if you had the following WHERE clause:
WHERE ID BETWEEN 100 and 150
a. All IDs starting with 100 and stopping with 150
b. All IDs starting with 101 and stopping with 150
c. All IDs starting with 100 and stopping with 149
d. All IDs starting with 101 and stopping with 149
11. If you need to remove a column value you need to use a(n)_________________ and to remove a table row you use a _________________:
a. DELETE statement, UPDATE statement
b. UPDATE statement, UPDATE statement
c. DELETE statement, DELETE statement
d. UPDATE statement, DELETE statement
12.To concatenate 2 or more column values you use:
a. &
b. –
c. +
d. *
13. Which of the following statements is true about the CONVERT and TRY_CONVERT functions?
a.
CONVERT can format the result of a conversion, but TRY_CONVERT can’t.
b.
CONVERT can be used with any data type, but TRY_CONVERT can’t.
c.
CONVERT returns an error if the expression can’t be converted, but TRY_CONVERT returns a NULL value.
d.
CONVERT is an ANSI-standard function, but TRY_CONVERT is not.
14. Given the following values in the column SalesAmt in the Sales table, what value would be returned by the following SQL statement?
SalesAmt
30
Null
0
20
50
SELECT AVG(SalesAMT) FROM SALES;
a. 20
b. 25
c. 33.333
d. Null
15. We need to create a table called Student that will have columns for StudentID, First Name, Last Name and MajorCode. What should be the data types of these fields?
a. TINYINT, Varchar(20), Varchar(50), CHAR(4)
b. INT, CHAR(20), CHAR(50), VARCHAR(4)
c. INT, Varchar(20), Varchar(50), CHAR(4)
d. INT, CHAR(20), CHAR(50), CHAR(4)
16. Primary keys and a foreign keys are:
a. The foreign key is a column that uniquely identifies it on the table and a primary key is a column on a table that is a primary key on another table
b. The primary key is a column that uniquely identifies it on the table and a foreign key is a column on a table that is a primary key on another table
c. The primary key and foreign key together provide a unique identifier for the table
d. The primary key is a column that uniquely identifies it on the table and the foreign key provides the default value for the primary key
17. If the date being evaluated is 2012-02-19, the functions DATENAME and DATEPART return what for the month?
a. February, 2
b. 2, February
c. Feb, 2
d. 2, Feb
18.You can use the OVER clause with an aggregate function to
a.
include the rows used to calculate the summary in the result set
b.
perform the summary over a range of values
c.
include values in the summary only if they’re over a specified amount
d.
omit summary rows with values over a specified amount
19. Given the following SQL, what order will be provided in the output?
SELECT LastName, First Name
FROM Customer
ORDER BY LastName, FirstName;
a. LastName ascending then FirstName ascending
b. LastName descending then FirstName ascending
c. LastName descending then FirstName descending
d. Won’t work, didn’t specify whether it was ascending or descending
20. I want to create a new table called Employees that will take the ID from the SalariedEmployees table and the ContractEmployees table. Which of the following SQL statements would do this?
a. SELECT EmpID
INTO Employees
FROM SalariedEmployees
UNION
SELECT ContID
FROM ContractEmployees;
b. SELECT EmpID
FROM SalariedEmployees
UNION
SELECT ContID
FROM ContractEmployees;
c. SELECT EmpID
INTO Employees
FROM SalariedEmployees
EXCEPT
SELECT ContID
FROM ContractEmployees;
d. SELECT EmpID
INTO Employees
FROM SalariedEmployees
INTERSECT
SELECT ContID
FROM ContractEmployees;
21. A correlated subquery is one that
a.
is executed once for each row in the outer query
b.
uses correlation names for one or more of the tables in a join
c.
uses correlation names for the tables in the subquery
d.
uses correlation names for the tables in the outer query
22. What is wrong with this SQL statement that returns all states minimum and maximum sales amounts that have an Average of at least $1000?
SELECT StateID, MIN(SalesAmt) as ‘MIN’, MAX(SalesAmt) as ‘MAX’
FROM Sales
WHERE AVG(SalesAmt) > 1000:
a. WHERE clause should not have AVG in it, should just be SalesAmt > 1000
b. Need to use a HAVING clause instead of a WHERE clause
c. Can’t write an SQL for this situation because you can’t filter on aggregate functions
d. Nothing is wrong, it will work as written.
23. You are creating a SELECT statement that returns data from 2 tables. You must return data from all rows in the first table and only the rows from the second table that match the join condition. Which type of JOIN should you use?
a. LEFT c. INNER
b. RIGHT d. FULL
24. Your company uses a table named Webstats to track daily traffic information for several Web sites. The table includes the following columns:
ID – stores the ID number for the website
Hits – stores the number of hits the site received that day
You must retrieve the average number of hits per day for each Web site. What should your run?
a. SELECT COUNT (*), AVG(hits)
FROM WebStats
GROUP BY hits;
b. SELECT SUM(hits)/COUNT(id)
FROM WebStats
GROUP BY id;
c. SELECT id, AVG(hits)
FROM WebStats
GROUP BY id;
d. SELECT AVG(hits)
FROM WebStats;
25. You are generating average salary statistics from a table containing employee information. You must calculate the average salary for each department, for each manager in each department, for each position under each manager in each department, and for all employees. What should you use in the GROUP BY clause of the SELECT statement?
a. ROLLUP(department, manager, position)
b. Department, manager, position
c. GROUPING SETS (department, manager, position), (department, manager), (department)
d. Nothing, it will be done automatically
26. Your company uses a table called Products to store information about the product it sells. The table includes the following columns:
Price – stores the sale price of the item
Volume – stores the average monthly sales volume for the item
Stock – stores the number of items currently in stock
At the beginning of each month, your company restocks a product if either of the following is true:
The item costs $100 or more and the number of items in stock is either less than the average monthly volume or less than 500
The item costs less than $100 and there are less than 1,500 in stock
You need to write an SQL statement that will retrieve all information about any items that need to be restocked. Which statement do you use?
a. SELECT * FROM Products
WHERE price >=100.00
AND stock < 500
OR stock < volume
OR price < 100.00
AND stock < 1500;
b. SELECT * FROM Products
WHERE (price >=100.00
AND stock < 500
OR stock < volume)
OR (price < 100.00
AND stock < 1500);
c.SELECT * FROM Products
WHERE (price >=100.00
AND (stock < 500
OR stock < volume))
OR (price < 100.00
AND stock < 1500);
d. SELECT * FROM Products
WHERE price >=100.00
AND stock < 500
OR stock < volume
OR (price < 100.00
AND stock < 1500);
27. Your company uses a table named Sales to track the total number of sales made by each employee. Each row contains an employee’s name and total number of sales that the employee has made while working for your company. Every time a sales is made it is entered into a table named Orders, along with the name of the employee who made the sale. At the end of each day, the Orders table is used to add the day’s sales to each employee’s sales count in the Sales table. If an employee has an entry in the Orders table that is not already in the Sales table a row for that employee is added. You must create an SQL that accomplishes this task. What statement(s) should you use?
a. A MERGE statement that updates the sales count for employees that are already in the Sales table and adds employees who are not
b. An UPDATE statement that updates the sales count for employees that are already in the Sales table and an INSERT statement that adds employees who are not in the Sales table.
c. An INSERT statement that adds employees who are not in the Sales table and that uses an OUTPUT INTO clause to update the sales count for employees that are already in the Sales table.
d. An UPDATE statement that updates the sales count for employees that are already in the Sales table and that uses an OUTPUT INTO clause to add employees who are not in the Sales table.
28. Your company recently changed its name from Coho Winery to Coho Vineyard. You must replace all references to “Coho Winery” in the productDescription column of the Products table with “Coho Vineyard” Which statement should you use?
a. UPDATE Products
SET productDescription = REPLACE(‘Coho Winery’, ‘Coho Vineyard’, productDescription);
b. UPDATE Products
SET productDescription = REPLACE(productDescription,‘Coho Winery’, ‘Coho Vineyard’);
c. UPDATE Products
SET productDescription = REPLACE(‘Coho Vineyard’, ‘Coho Winery’, productDescription);
d. UPDATE Products
SET productDescription = REPLACE(‘Coho Vineyard’, ‘Coho Winery’, productDescription);
29. Your company stores employee information in a table created with the following:
TABLE Employees: name varchar(20)
department varchar (20)
4 employees were recently hired and need to be added to the Employee’s table which of the following sets of code would not work?
Claus Hansen in the Sales department
Wilson Pais in the Sales department
Miles Reid in the Marketing department
Doris Krieger in the HR department
a. INSERT INTO Employees
VALUES (‘Clause Hansen’, ‘Sales’), (‘Wilson Pais’, ‘Sales’), (‘Miles Reid’, ‘Marketing’), (‘Doris Krieger’, ‘HR’);
b. INSERT INTO Employees (name, department)
VALUES (‘Clause Hansen’, ‘Sales’), (‘Wilson Pais’, ‘Sales’), (‘Miles Reid’, ‘Marketing’), (‘Doris Krieger’, ‘HR’);
c. INSERT INTO Employees
VALUES (‘Sales’, ‘Clause Hansen’), (‘Sales’, ‘Wilson Pais’), (‘Marketing’, ‘Miles Reid’), (‘HR’, ‘Doris Krieger’);
d. INSERT INTO Employees (name, department)
VALUES (‘Clause Hansen’, ‘Sales’);
INSERT INTO Employees (name, department)
VALUES (‘Wilson Pais’, ‘Sales’);
INSERT INTO Employees (name, department)
VALUES (‘Miles Reid’, ‘Marketing’);
INSERT INTO Employees (name, department)
VALUES (‘Doris Krieger’, ‘HR’);
30. Your company uses a database named Sales to track orders placed by salespeople. You have retrieved the total number of each product sold and total revenue generated by each product using the statement
SELECT product, SUM(quantity), SUM(quantity * price)
FROM Sales
GROUP BY product;
You need to modify the statement to retrieve only those products whose total revenue is greater than $100,000. What do you do?
a. Add a WHERE clause to the SELECT statement that checks if SUM(quantity * price) is greater than $100,000
b. Add a WHERE clause to the SELECT statement that checks if quantity * price is greater than $100,000
c. Add a WHERE clause to the GROUP BY statement that checks if SUM(quantity * price) is greater than $100,000
d. Add a HAVING clause to the GROUP BY statement that checks if SUM(quantity * price) is greater than $100,000
a.
200.00
c.
25
b.
225.00
d.
0
Explanation / Answer
Hey do not post these many in one post .As per Chegg guidelines you can post 2 or 3 question not more than that.
1. b
2. c
3. c
4. d
5. a
6. d
7. b
8. c
9. e
10. a
11. c
12. d
13. c
14. a
15. c
16. b
17. a
18. d
19. a
20. b
21. d
22. d
23. a
24. c
25. c
26. a
27. b
28. b
29. d
30. a
**Comment for any further queries.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.