Tricky sql queries for interview - SQL Tutorial

Tricky sql queries for interview

1) Sql queries to create database
          Create database dbname

      *A database can’t be dropped while it will be in use or running mode.
 * To drop a database we have to use another database but it will also not
   dropped till it will be in running mode

     2) Sql queries to drop a database

   Drop database dbname (It will be dropped using another database if it will not be in use)

     3) Sql queries to rename  a database

       sp_renamedb 'dbName','NewdbName'

4) Sql queries to create a table

createtable Emp
 RID intprimarykeyidentity(1,1),
 Name varchar(50),
  Age int,
  salary int

     5) Sql queries to rename a table
sp_rename 'Emp','employee'

     6) Sql queries to rename a column

     7) Sql queries to alter data type of column

altertable employee altercolumn emp_age varchar.
(a datatype of column can not be changed when it will have data  inside it and it can be changed also when it will have data too but only in case of upcasting  ex:- int to varchar)

    8) Sql queries to add new column in table
altert table employee add gender varchar(50)

alter table employee add EmailId varchar(50)

alter table employee add EmpID int(50)

Note: The alter keyword is use for change the defnition of databse object(like procedure ,function and table )

    9) To drop a column from table

altertable employee dropcolumn gender

         Some Select commands to featch record from SQL

  10)   To fetch two and more records from table on the basis of ID

select*from employee where ID=or ID=5

output:-  ID   NAME      AGE    SALARY

             2        Karan        24      23222
             5        virat           28      10000000

11) To find the same using IN func

      select*from employee where ID in(2,4)

      output:-    ( ID, name,   age, salary)
                        2    Karan    24  23222
                        4    Mohan   76  29977

12) Another query using not operator find exept the given id

     select*from employee where RID notin(2,4)
     output:- ( RID, NAME, AGE,SALARY)
                     1    yogesh    23    25000
                     3    Ethen      08   200
                     5    virat        28  10000000
                     6    sachin     41   19000000

13) To select or fetch data between two no.’s

    select*from employee where RID between 3 and 5

    output:- ( ID, NAME, AGE, SALARY)
                   3      Ethen     08     200
                   4      Mohan  76     29977
                   5      virat      28     10000000

(whenever we will use between function then it’ll always consider start and end no. Ex:- if we want to select 2,4,5,6 then provide range from 2 to 6 including 1st and last)

14)To select data in order of name(use of order by)

    select*from employee orderby Name

    output:- (ID,  NAME,  AGE,  SALARY)
                    3      Ethen    08      200
                    2      Karan    24      23222
                    4      Mohan  76       29977
                    6      sachin   41      19000000
                    5      virat      28       10000000
                    1      yogesh 23        25000

   We use here desc to display data in decending order using this query

   select*from employee orderby Name desc 
           1 yogesh   23  25000
           5 virat        28 10000000
           6 sachin    41  19000000
           4 Mohan   76   29977
           2 Karan     24   23222
           3 Ethen    08   200