For beginners to intermediate levels, this is a quick test/assignment to check how familiar they are with SQL. Such types of exercises will certainly help to improve your skills. Practice and exercise problems are the easiest way to learn something.
This has nothing to do with Microsoft exams or their pattern. This is just to test your SQL skills. The test involves 5 parts concentrating on the database development. To complete the test, you need to use SQL built-in/system functions, arithmetic operators, different clauses, stored procedures, user-defined functions, and triggers.
Below are the datasets for the exam.
Dataset-1: Students
INSERT STATEMENTS:
Click here.
Dataset-2: Emp & Dept
INSERT STATEMENTS:
Click here
Assignment (from the dataset-1):
1) Fetch the students who haven’t provided their phone numbers.
2) Write a query to display how much the actual fee will be if a 10% discount is given.
3) Write a stored procedure to update the balance after deducting 10% from the fee.
4) Write a query to display the top slow learners in the class.
5) Display the number of students from each city.
Assignment (from the dataset-2):
1) Write a query to display the employee and manager’s names.
2) Fetch the employee’s name and his department name.
3) Who is the second highest salaried employee on the list?
4) List the first employee who has been with the company from the beginning and who has joined lately.
5) Users are permitted to insert the records from the Emp table; hence, create a trigger for audit maintenance and store the historical information in the EmpAudit table.
Theoretical Questions:
Q1. What is normalisation?
Q2. What is a transaction?
Q3. What do triggers do?
Q4. What is a primary key?
Q5. What are implicit and explicit commits?
Multiple Choice Questions:
Q1. The requirement is to join two tables: customers and orders. All the customer’s details should be displayed regardless of whether they ordered earlier. Which join condition is to be applied?
a) Normal b) Left Outer c) Right Outer d) Full Outer
Q2. The string “Smith Miller” is retrieved by combining “firstName” and “lastName” from a table using a function. What was that function?
a) CAST b) CONVERT c) ADD d) CONCAT
Q3. ……….. is a sequence of operations performed (using one or more SQL statements) on a database as a single logical unit of work; All the operations in that sequence either be committed or rolled back at the end.
a) Trigger b) Transaction c) Cursor d) Stored Procedure
Q4. …………. returns the count of open transactions in the current session.
a) COUNT b) * (asterisk) c) @@TRANCOUNT d) @@OPENCOUNT
Q5. …………. is a temporary table usually created with a “declare” statement.
a) Temp tables b) Global Variables c) Local Variables d) Table Variables
Identify the missing/incorrect syntax. Rewrite it.
Q1. CREATE TRIGGER tr_Ins_EmpInsert ON Emp
AS
BEGIN
<SQL STATEMENTS…>
END
Q2. CREATE viewEmpDept AS
SELECT EmpNo, EName, DName, Sal FROM Emp
JOIN DEPT ON Emp.DEPTNO = Dept.DEPTNO
Q3. INSERT INTO Table1(StudentName, StudentCity, PaidAmount) VALUES
(‘Zafar Iqbal’, 20000, ‘Dubai’)
Q4. CREATE uspSelectAccess PROCEDURE(@UserID, @username, @function)
AS
BEGIN
SELECT * FROM tbAccessControl
WHERE UserID = @UserID AND @UserName = @UserName AND vcfunction = @function
END
Q5. DECLARE @1 AS VARCHAR(2)
SET @1 = ’18/03/2015 08:07:25 am’
SELECT CONVERT(datetime, @1,103)
Send a message for answers.
Big Data & SQL 
One comment