SQL Assignment-1

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 skills. Practice and exercise problems are the easiest way to learn something.

This has nothing to do with Microsoft exams or its pattern. This is just to test your SQL skills. The test involves 5 parts concentrating on the database development. In order to complete the test, you need to use SQL built-in /system functions, arithmetic operators, different clauses, stored procedures, user-defined functions and triggers.

The 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 student’s information who haven’t provided their phone numbers.
2) Write a query to display how much the salary will be if 10% discount is given.
3) Write a stored procedure to update the balance after deducting 10% from the actual fee.
4) Write a query to display 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 name and his manager’s name.
2) Fetch employee name and his department name.
3) Who is second highest salaried employee in the list?
4) List out the first employee who is with the company from the beginning and who is joined lately.
5) Users are permitted to insert the records from Emp table hence create a trigger for audit maintenance and store.

Theoretical Questions:
Q1.        What is normalization?
Q2.        What is a transaction?
Q3.        What triggers do?
Q4.        What is a primary key?
Q5.        What is implicit and explicit commits?

Multiple Choice Questions:
Q1. Requirement is to join two tables Customers and Orders. All the customers details should be displayed regardless they have ordered earlier. Which join condition 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.  …………. are kinds of variables that allow you to hold rows of data, which are similar to temporary tables.
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.