SQL server Interview Question – Part 1

Web API 2- .Net Core In depth In 5 Hours click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/

What is data?

Data is a collection of information. Data means data can be any information. For example there is an employee and all the information about the employee like (Emp id, Emp Name, Emp Age, Emp Mobile Number, Emp Address, Emp Salary, Emp Designation). 

What is Data Base?

Database is a systematic collection of data, if you considering employee all the information about employee like Emp id, Emp Name, Emp Age, Emp Mobile Number, Emp Address, Emp Salary, Emp Designation these all details are data and if you are organizing that data in a systematic collection of manner then that is called Database.

Why we organizing data systematically?

Data can be easily accessed, managed and updated.

What is DBMS (Data Base Management System)?

Data Base Management System is software like SQL server, MySQL, and Oracle, they provide option for us to create or manage a database management system and also provide options for insert, update, delete, and give access to data in database management system. So the purpose of Data Base Management System is to help us to create or maintain a database. DBMS facilitates the process of defining, constructing, manipulating, and sharing data among various users. 

What is SQL?

SQL stands for Structured Query Language. SQL is a just language, and language is way to communicate with the database using SQL.  SQL is used for maintaining the relational database.

What are the usages of SQL?

·       To execute queries against a database.

·       To retrieve data from a database.

·       To inserts, updates records in a database.

·       To create new database or table.

How SQL working?

When user want to create the database and he will write some code in SQL and send the request to database and then database will execute the request, process the request, and once everything is fine database will execute and send the result to user.  

There are 5 commands in SQL:

1.        DQL (Data Query Language) >> Select.

2.        DDL (Data Definition Language) >> Create – Alter – Drop – Truncate – Rename.

3.        DML (Data Manipulation Language) >> Insert – Delete – Update.

4.        TCL (Transaction Control Language) >> Commit – Rollback – Savepoint.

5.        DCL (Data Control Language) >> Grant – Revoke.

What is DQL (Data Query Language)?

Data query language means kind of instructions we use to get or retrieve the data which is we saved in the database. 

What is DDL (Data Definition Language)?

Data definition language you can create database or changed database or remove database or rename database using this command.

What is DML (Data Manipulation Language)?

Data Manipulation Language in that we can insert new data or removing saved data or updating saved data using this command.

What is TCL (Transaction Control Language)?

Transaction Control Language manages the transactions in the database.

What is DCL (Data Control Language)?

Data Control Language used to control access to data stored in a database.

Explain Select statement?

Retrieve the data from database.

Syntax

1.        SELECT *
FROM   table_name;// for all column retrieve from table.

For example

SELECT *
FROM   employee_info  // then you get all data from the employee table with all columns.

2.SELECT column1,
       column2…
FROM   table_name;// for one or more column retrieve from table.

For example

SELECT empid,
       empname,
       empsalary,
       empmobilenumber
FROM   employee_info 

3. If we want retrieve few records then we need to use condition.

SELECT column1,
       column2…
FROM   table_name
WHERE  CONDITION; // for few record retrieve from table.

For example

SELECT empid,
       empname,
       empage
FROM   employee_info
WHERE  empage > 20;

Some other example with their outputs:

Select 1                             >>          1

Select 1 as Zero               >>          Column name Zero and in row 1.

Select 1 + 2 as number   >>          Column name number and in row 3.

Select ‘1’ as Character   >>          Column name Character and in row 1.

Select ‘1’ + 2 as number               >>          Column name number and in row 3.

Select ‘1’ + ‘2’ as String                >>          Column name String and in row 12(here is 1 and 2 are concatenate).

Select ‘1’ + ‘2’ + ‘ XYZ’ as String  >> Column name String and in row 12 XYZ.

Select ‘1’ + ‘A’ as String                >>          Column name String and in row 1A.        

Select 1 + ‘A’ as String    >>          error (conversion failed when converting the varchar value)

Web API 2- .Net Core In depth In 5 Hours click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/

How to Sorting Data in SQL?

If you want to sort data for ascending order means A to Z, then you can use following syntax:

SELECT column_name
FROM   table_name
ORDER  BY column_name; 

Or

SELECT column_name
FROM   table_name
ORDER  BY column_name ASC; 

If you want to sort data for descending order means Z to A, then you can use following syntax:

SELECT column_name
FROM   table_name
ORDER  BY column_name DESC; 

How to create table in SQL?

CREATE TABLE table_name
  (
     column_1 DATATYPE,
     column_2 DATATYPE,
     column_3 DATATYPE
  ) 

For Example

CREATE TABLE employee_info
  (
     emp_id      INT,
     emp_name    VARCHAR(25),
     emp_age     INT,
     emp_mnumber VARCHAR(25)
  ); 

How to insert data in table?

INSERT INTO table_name
            (column1,
             column2,
             column3,
             ... columnn)
VALUES      (value1,
             value2,
             value2,
             …valuen); 

For example

INSERT INTO employee_info
            (emp_id,
             emp_name,
             emp_age)
VALUES      (1,
             ‘abc’,
             25); 

How to update data in table?

UPDATE table_name
SET    column1 = value1,
       column2 = value2,
WHERE  condition;

For example

UPDATE employee_info
SET    emp_age = ‘15’
WHERE  emp_id = 3;

How to remove data from table?

If you want to remove data then you need to use DELETE statement.

DELETE FROM table_name
WHERE  CONDITION; 

For example

DELETE FROM employee_info
WHERE  emp_name = ‘abc’;

How to add more columns and data in existing table?

If we want to add column in existing table then we need to use ALTER TABLE. ALTER TABLE statement is used to add, delete, or modify columns and add and drop various constraints in an existing table.

ALTER TABLE table_name
  ADD column_name DATATYPE; 

For example

ALTER TABLE employee_info
  ADD emp_gender VARCHAR(25);

Remove column in existing table

ALTER TABLE table_name
  DROP COLUMN column_name;

For example

ALTER TABLE employee_info
  DROP COLUMN emp_gender;

What do you mean by table and field in SQL?

Table >> A table refers to a collection of data in an organized manner in form of rows and columns.

For example: Employee_info

Field >> A field refers to the number of columns in a table.

For example: Emp_id, Emp_Name, Emp_Age

Why we use BETWEEN and IN operator?

The BETWEEN operator is used to fetch rows based on a range of values.

SELECT *
FROM   table_name
WHERE  column1 BETWEEN 1 AND 3;

So here output is 1 to 3 rows display. 

The IN operator is used to check for values contained in specific sets.

SELECT *
FROM   table_name
WHERE  column1 IN ( 1, 3 );

So here output is only 1 and 3 rows are display.

What is mean by NULL?

A field with the null value is field with a no value or absence of a value.

What is the difference between Primary key and Unique key?

·       Primary key is all the values are unique and it can’t accept single null.

Unique key is accepting only one null value.

·       A table can have only one primary key.

Unique key there can be multiple unique key on a table.

·       When defined primary key, a clustered index automatically created.

Unique key generates the non-clustered index.

Web API 2- .Net Core In depth In 5 Hours click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/

Visit our website: https://www.expertwithsagarjaybhay.com/