SQL Exercise 1 - SQL Exercises for Beginners

SQL Exercises for Beginners / Simple SQL Exercises with Answers / SQL Exercises for simple Table creation and SELECT queries

Question:

[A] Table Creation

Create a table according to the schema given below. Choose the appropriate data types while creating the table. Insert the records given in Table 1 into the Employee table. And, write SQL queries to satisfy the questions given below.

Employee (Emp_ID, Emp_Name, DoB, Department, Designation, DoJ, Salary)

Here, DoB means Date of Birth, DoJ means Date of Joining.

Emp_ID	Emp_Name	DoB	Department	Designation	DoJ	Salary
F110	Sam	15-JUN-1970	Bio-Technology	Professor	12-APR-2001	45000
F111	Kumar	25-MAY-1980	Mechanical	Asst. Prof.	02-MAY-2006	30000
F115	Raguvaran	10-AUG-1982	CSE	Asst. Prof.	05-MAY-2007	27000
F114	Jennifer	10-SEP-1975	CSE	Asst. Prof.	03-JUN-2004	35000
F117	Ismail	15-MAY-1979	IT	Asst. Prof.	10-MAY-2005	33000

Table 1 – Employee

[B] Queries

1. Display all the records from table Employee.

2. Find all the employees who are working for CSE department.

3. Get the details about the employees who have joined after ’10-JUN-2005’.

4. Find all the employees who earn more than 30000.

5. Get the details of employees who are not ‘Professor’.

6. Find the name, date of birth, and designation of all the employees who work for ‘IT’ department.

7. Find all the departments which are offering salary above 25000.

8. Get the DoB of employee named ‘Kumar’.

9. Find the names and departments of employees who earn the salary in the range 20000 to 40000.

10. Find the employee details of any employee who work for ‘CSE’ and earn more than 30000.

********************

Answers:

[A] Table Creation

The following CREATE TABLE statement will create the table according to the specification given in question.

CREATE TABLE Employee (

Emp_ID CHAR(3),

Emp_Name VARCHAR(30),

DOB DATE,

Department VARCHAR(20),

Designation VARCHAR(15),

DoJ DATE,

Salary Number(10,2));

Explanation:

Whatever mentioned in CAPITAL LETTERS are keywords/reserved words.

I have used various datatypes for various attribute declaration.

CHAR data type used for Emp_ID attribute as I would know the exact size of any employee ids.

VARCHAR data type used for Emp_Name, Department, and Designation attributes, because the values stored in those columns may vary. That is, for example, a name would be just 3 characters long or 30 characters long. Try to learn the difference between CHAR and VARCHAR.

DATE data type used for all the attributes where I need to store dates (DoB, and DoJ).

NUMBER is used for salary as I would like to store salary values. In the above declaration (10, 2) means that any values upto the size of total 10 digits out of which 2 digits after decimal point.

The CREATE TABLE statement is terminated with ‘;’ as any other statements in SQL.

Data Insertion

All the records given in Table 1 can be inserted using the following INSERT INTO statement.

INSERT INTO Employee VALUES (‘F110’, ‘Sam’, ‘15-JUN-1970’, ‘Bio-Technology’, ‘Professor’, ‘12-APR-2001’, 45000);

As stated above, the words in CAPITAL LETTERS are keywords/reserved words.

Observe that how the information are specified for every column. You must mention the values in the order in which you have declared the attributes in the table, with the following simple rules;

          CHAR, VARCHAR, and DATE attribute values should be given inside a pair of single quotes.

          NUMBER inputs can be specified without any quotes.

[B] Queries

Before go into SELECT queries, recall the structure of a SELECT query. Basically, we need at least two clauses, SELECT and FROM to write a complete query. The questions given above need the clauses, SELECT, FROM, and WHERE. See below for the clauses with the parameters needed;

SELECT */list of attributes to be included in the result

FROM list of one or more tables

WHERE list of conditions ANDed, ORed, or Negated (NOT)

1. Display all the records from table Employee.

For question 1, we need to write a query that displays the entire table. For writing this query, we need only two clauses, SELECT, and FROM as follows;

SELECT *

FROM Employee;

The parameter for SELECT clause, ‘*’ represents all the columns/attributes of the table parameter which is given in FROM clause. You may write the query in single line also.

The result is Table 1 as it is.

2. Find all the employees who are working for CSE department.

Question 2 includes a condition. The condition is ‘the employees working for CSE department’. Hence, we have a parameter for WHERE clause. The parameter given in the WHERE clause have a form;

Attribute_name θ value

Here, θ would mean any valid comparison operators (=, <, >, <=, >=, <>). Then, the query would be written as follows;

SELECT *

FROM Employee

WHERE department = ‘CSE’;

Result:

Emp_ID	Emp_Name	DoB	Department	Designation	DoJ	Salary
F115	Raguvaran	10-AUG-1982	CSE	Asst. Prof.	05-MAY-2007	27000
F114	Jennifer	10-SEP-1975	CSE	Asst. Prof.	03-JUN-2004	35000

3. Get the details about the employees who have joined after ’10-JUN-2005’.

The condition given in the question is Date of Joining. The query is,

SELECT *

FROM Employee

WHERE DoJ > ’10-JUN-2005’;

Result:

Emp_ID	Emp_Name	DoB	Department	Designation	DoJ	Salary
F111	Kumar	25-MAY-1980	Mechanical	Asst. Prof.	02-MAY-2006	30000
F115	Raguvaran	10-AUG-1982	CSE	Asst. Prof.	05-MAY-2007	27000

4. Find all the employees who earn more than 30000.

The condition is about Salary attribute.

SELECT *

FROM Employee

WHERE Salary > 30000;

Result:

Emp_ID	Emp_Name	DoB	Department	Designation	DoJ	Salary
F110	Sam	15-JUN-1970	Bio-Technology	Professor	12-APR-2001	45000
F114	Jennifer	10-SEP-1975	CSE	Asst. Prof.	03-JUN-2004	35000
F117	Ismail	15-MAY-1979	IT	Asst. Prof.	10-MAY-2005	33000

5. Get the details of employees who are not ‘Professor’.

The condition involves the attribute Designation.

SELECT *

FROM Employee

WHERE Designation <> ‘Professor’;

Result:

Emp_ID	Emp_Name	DoB	Department	Designation	DoJ	Salary
F111	Kumar	25-MAY-1980	Mechanical	Asst. Prof.	02-MAY-2006	30000
F115	Raguvaran	10-AUG-1982	CSE	Asst. Prof.	05-MAY-2007	27000
F114	Jennifer	10-SEP-1975	CSE	Asst. Prof.	03-JUN-2004	35000
F117	Ismail	15-MAY-1979	IT	Asst. Prof.	10-MAY-2005	33000

6. Find the name, date of birth, and designation of all the employees who work for ‘IT’ department.

Question 6 is different from all the above 5. It needs different parameters other than *. That is, we need to display only what columns (attributes required). We need to display name, DoB, and Designation of all employee records which satisfy the condition “Department = ‘IT’”.

SELECT Emp_Name, DoB, Designation

FROM Employee

WHERE Department = ‘IT’;

Here, the parameters for the SELECT clause are separated using commas (‘,’).

Result:

Emp_Name	DoB	Designation
Ismail	15-MAY-1979	Asst. Prof.

7. Find all the departments which are offering salary above 25000.

For this question we need to display Departments. The condition to be satisfied is Salary > 30000.

SELECT Department

FROM Employee

WHERE Salary > 25000;

Result:

Department

Bio-Technology

Mechanical

CSE

CSE

IT

8. Get the DoB of employee named ‘Kumar’.

We need Date of Birth. The condition involves Emp_Name attribute.

SELECT DoB

FROM Employee

WHERE Emp_Name = ‘Kumar’;

Note: The parameters like the list of attribute names, the list of table names, and even the keywords are CASE INSENSITIVE. But, the literal values specified between the set of quotes are CASE SENSITIVE. Hence, the above query would return a result only if you have a record with the Emp_Name value as ‘Kumar’. If you have ‘kumar’, it cannot include that record in the result.

Result:

DoB

25-MAY-1980

9. Find the names and departments of employees who earn the salary in the range 20000 to 40000.

We need to display the columns Emp_Names, and Departments. The condition is a range of values (salary). It can be written in two ways at least;

(a)     SELECT Emp_Name, Department

FROM Employee

WHERE Salary >= 20000 AND Salary <= 40000;

(b)     SELECT Emp_Name, Department

          FROM Employee

          WHERE Salary BETWEEN 20000 AND 40000;

Here, you can find two new things. The first one is how do we mention two or more conditions in the WHERE clause using AND logical connective. The second is the keyword BETWEEN, which is used to mention a range of values. The simple syntax for that would be as follows;

…

WHERE attribute BETWEEN value1 AND value 2.

Result:

Emp_Name	Department
Kumar	Mechanical
Raguvaran	CSE
Jennifer	CSE
Ismail	IT

10. Find the employee details of any employee who work for ‘CSE’ and earn more than 30000.

This question requires all the records from Employee table. The conditions are the Department and Salary.

SELECT *

FROM Employee

WHERE Department = ‘CSE’ AND Salary > 30000;

Result:

Emp_ID	Emp_Name	DoB	Department	Designation	DoJ	Salary
F114	Jennifer	10-SEP-1975	CSE	Asst. Prof.	03-JUN-2004	35000

Microsoft Certification

Microsoft Based Database Certifications Programs / Microsoft SQL Server Certification Options / List of Database Certification Programs Worldwide

Microsoft certification programs test your knowledge and skills in designing, building and maintaining the next wave of cloud-ready database and information solutions for the IT and database industries.

Certification	Level	You have to pass	Description
MTA Database Track – Microsoft Technology Associate	Entry	Database Fundamentals	This level is for those intending to build a career in data platform administration or business intelligence
MCSA - Microsoft Certified Solutions Associate	Intermediate	Step 1 – Querying Microsoft SQL Server 2012 2 - Administering Microsoft SQL Server 2012 Databases 3 - Implementing a Data Warehouse with Microsoft SQL Server 2012	This certification will help you to find jobs like database developer and database analyst
MCSE: Data Platform – Microsoft Certified Solutions Expert	Advanced	Step 1 – Querying Microsoft SQL Server 2012 2 - Administering Microsoft SQL Server 2012 Databases 3 - Implementing a Data Warehouse with Microsoft SQL Server 2012 4 - Developing Microsoft SQL Server 2012 Databases 5 - Designing Database Solutions for SQL Server 2012	This certification will help you to find jobs like database analyst and designer
MCSE: Business Intelligence - Microsoft Certified Solutions Expert	Advanced	Step 1 – Querying Microsoft SQL Server 2012 2 - Administering Microsoft SQL Server 2012 Databases 3 - Implementing a Data Warehouse with Microsoft SQL Server 2012 4 - Implementing Data Models and Reports with Microsoft SQL Server 2012 5 - Designing Business Intelligence Solutions with Microsoft SQL Server 2012	This certification could get you a BI and reporting engineer jobs

Note: Several other old SQL Server certifications can be converted into the new certifications through upgradation. You can find more on here, https://www.microsoft.com/learning/en-in/sql-certification.aspx

MC 7103 – DATABASE MANAGEMENT SYSTEMS January 2014 Question Paper

MC 7103 – Database Management Systems - January 2014 Question Paper / Anna University MCA Exam January 2014 Question Paper / MC7103 DBMS January 2014 Question Paper / MC7103 Database Management Systems Previous Year Question Paper with Answers / Anna University Affiliated Colleges MCA Question Papers / Regulation 2013 MC7103 Question Paper

Question Paper Code : 80813

M.C.A DEGREE EXAMINATION, JANUARY 2014

MC 7103 – DATABASE MANAGEMENT SYSTEMS

(Regulation 2013)

Time : Three Hours                                                         Maximum : 100 marks

Answer ALL Questions

PART A – (10 X 2 = 20 marks)

1. Differentiate file systems from a database system.

2. What is weak entity sets? Give examples.

3. Define the “integrity rules”

4. What is a view? How it is related to data independence?

5. What do you understand from Time stamping? Give examples.

6. What is shadow paging? When it is used?

7. What is the use of unstructured file? Give an example.

8. When do we need to do indexing and hashing?

9. What is persistent data? Give an example.

10. What are mobile databases? Give an example.

PART B – (5 X 16 = 80 marks)

11. (a) Explain the various levels of database schema based on the data abstraction. How does the schema support different type of data independence? (16)

Or

(b) Scenario: A publishing company produces scientific books on various subjects. The books are written by authors who specialize in one particular subject. The company employs editors who, not necessarily being specialists in a particular area, each take sole responsibility for editing one or more publications. A publication covers essentially one of the specialist subjects and is normally written by a single author. When writing a particular book, each author works with one editor, but may submit another work for publication to be supervised by other editors. To improve their competitiveness, the company tries to employ a variety of authors, more than one author being a specialist in a particular subject.

Draw an ER diagram that represents the above scenario and also identify the thing like entities, attributes of entities, all keys, relationship among the entities, cardinality/connectivity between entities and any assumption you make. (16)

12. (a) Consider the following relational schema. An employee can work in more than one department; the pct_time field of the Works relation shows the percentage of time that a given employee works in a given department.

Emp(eid: integer, ename: string, age: integer, salary: real)

Works(eid: integer, did: integer, pct_time: integer)

Dept(did: integer, dname: string, budget: real, managerid: integer)

Write the following queries in SQL;

(i) Print the names and ages of each employee who works in both the Hardware department and the Software department.

(ii) Find the managerids of managers who manage only departments with budget greater than Rs. 1 lakhs.

(iii) Find the enames of managers who manage the departments with the largest budgets.

(iv)  If a manager manages more than one department, he or she controls the sum of all the budgets for those departments. Find the managerids of managers who control more than Rs. 5 lakhs.

(v) Find the managerids of managers who control the largest amounts.

(vi) Find the enames of managers who manages only departments with budgets larger than Rs. 1 lakhs, but at least one department with budget less than Rs. 5 lakhs. (16)

Or

(b) (i) Why do we need normalization? Is normalization preferred in today’s scenario? (4)

(ii) Explain the different normal forms with an example. (12)

13. (a) (i) Explain about transaction properties. (8)

(ii) Explain the SQL commands that support transaction with an example. (8)

Or

(b) (i) Describe about different types of recovery techniques. (10)

(ii) Describe about security of statistical databases. (6)

14. (a) Explain about different types of hashing and collision resolution techniques. (16)

Or

(b) Write short notes on;

          (i) Sequential file operation. (8)

          (ii) B+ tree. (8)

15. (a) Write short notes on the following databases;

          (i) Temporal databases. (8)

          (ii) Spatial databases. (8)

Or

(b) (i) Write a comparison between ODBMS and RDBMS. (8)

(ii) Write a short note on XML. (8)

_____________________