Q.1:- How many Subset of SQL ?
There are three main subset of the SQL.
1:-DDL (Data Definition Language) – This subset allows you to perform various operations on the database such as CREATE, ALTER and DELETE objects.
2:-DML ( Data Manipulation Language) – This subset allows you to access and manipulate data. Such as insert, update, delete and retrieve data from the database.
3:-DCL ( Data Control Language) – This subset allows you to control access to the database. Example – Grant, Revoke access permissions
Q.2:- What is DBMS ?
Database Management System is a application software that interacts with the user, application and the database itself to capture and analyze data.
DBMS is allow a user to interact with database. The data stored in the database can be modified, retrieved and deleted.
Q.3:- What do you mean the table and field in SQL?
A table refers to a collection of data in a organised manner in form of rows and columns. A field refers to the number of columns in a table. For example:
Table: tblstudent
Field: Stu Id, Stu Name, Stu Marks.
Q4. What are joins in SQL?
A JOIN clause is used to combine rows from 2 or more tables, based on a related column between them. It is used to merge 2 tables or retrieve data from there. There are 4 types joins in SQL namely:
Inner Join
Right Join
Left Join
Full Join
Q5..create a table with the use of left outer join from two tables.
The LEFT JOIN returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
I have 2 table tblemployee and department.
tblemployee
2- department
Example of left Join
select firstName,lastName,salary,e.department_id from tblemployee e left join department d on e.department_id=d.department_id order by department_id
Q 6.Write query for select top 3 values from salary column.
SELECT * FROM tblemployee WHERE (salary IN ( SELECT TOP (3) salary FROM tblemployee GROUP BY salary ORDER BY salary DESC))
salary= column name
tblemployee = tbl name
Q 7.What is use of rank() ?
The RANK() function is a window function that assigns a rank to each row.
Example:- I have a table tblemployee
Let us use RANK() to assign ranks to the rows in the result set of tblemployee table :
SELECT * , RANK () OVER (ORDER BY salary) AS Rank_no FROM tblemployee;
|
FirstName |
LastName |
Gender |
Salary |
HireDate |
department_id |
Rank_no |
|
sajneet2 |
Kaur |
Female |
2000 |
00:00.0 |
7 |
1 |
|
Priyanka |
Kumari |
Female |
3000 |
00:00.0 |
4 |
2 |
|
lokesh |
Kumar |
Male |
4000 |
00:00.0 |
3 |
3 |
|
Jon |
Jony |
Male |
5000 |
00:00.0 |
2 |
4 |
|
Ankit |
Shukla |
Male |
8000 |
00:00.0 |
1 |
5 |
