SQL Queries with example in DBMS PDF

Preview

Summary

SQL Queries with example in DBMS...

Description

SQL Queries 1. Listing of databases Query/Syntax :- show databases;

2. Creation of database Syntax:- create database database_name; Query:- create database employee;

3. Using the database Syntax:- use database_name; Query:- use employee;

4. Creation of table Syntax:- Create table table_name(attribute datatype(size));

Query:- create table emp (e_name varchar(20), e_city varchar(20),salary numeric, e_ID integer primary key, Address varchar(50),Department varchar(20));

5. Displaying the tables Query/Syntax:- show tables;

6. Describes the table Syntax:- describe table_name; Query:- describe emp;

7. Inserting values into table Syntax:- insert into table_name values(attribute datatype(size)); Queries:Insert into emp values(‘Pooja’,’Rajkot’,56825,1008,’Paschim vihar’,’Computer science’); Insert into emp values(‘Anisha’,’Delhi’,100000,1204,’Shalimar bagh’,’IT’); Insert into emp values(‘Sakshi’,’Vadodra’,62842,2015,’Rajendra place’, ’Accounts’); Insert into emp values(‘Varun’,’Kanpur’,56829,3042,’Vivek vihar’,’marketing’); Insert into emp values(‘Prateek’,’Delhi’,25686,4100,’Preet vihar’,’Housing department’); Insert into emp values(‘Nidhi’,’Hyderabad’,78000,4102,’Janakpuri’,’Advertising’); Insert into emp values(‘Sagar’,’Delhi’,65400,4500,’Wazirpur’,’Customer service’); Insert into emp values(‘Neha’,’Chennai’,56000,4506,’Model town’,’Accounts’); Insert into emp values(‘Alka’,’Bengaluru’,89726,4603,’Moti nagar’,’Customer service’); Insert into emp values(‘Ayushi’,’Delhi’,66784,5018,’Dwarka’,’Inventory management’); Insert into emp values(‘Nisha’,’Ahmdabad’,65000,5601,’Uttam nagar’,’Production’); Insert into emp values(‘Priya’,’Bengaluru’,25686,5604,’Rajouri garden’, ‘marketing’); Insert into emp values(‘Saurabh’,’Surat’,75800,5671,’Shahdra’,’Advertising’);

Insert into emp values(‘Piyush’,’Bengaluru’,73000,6013,’Kashmere gate’,’IT’); Insert into emp values(‘Nitesh’,’Jaipur’,89000,6018,’Ashok park’,’R&D’); Insert into emp values(‘Deepika’,’Delhi’,45000,7803,’Cannaught place’,’Sales’); Insert into emp values(‘Mandeep’,’Jodhpur’,50000,8172,’Kirti nagar’,’Finance’); Insert into emp values(‘Divesh’,’Mumbai’,46000,8858,’Jahangir puri’,’Human resource’); Insert into emp values(‘Swati’,’Indore’,45000,8908,’Ashok vihar’,’marketing’); Insert into emp values(‘Sahil’,’Delhi’,35500,8912,’Karol bagh’,’Accounts’); Insert the values into table. Data of type varchar only is to be written in single quotes.

8. Shows the contents of table Syntax:- select *from table_name; Query:- select *from emp;

9. Creating a table and also defining foreign key Syntax:- create table new_table_name(attribute datatype(size),foreign key(attribute) references old_ table_name(attribute(Primary key)); Query:- Create table company(c_name varchar(30),c_city varchar(20), emp_ID integer, foreign key(emp_ID) references emp(e_ID));

Queries:Insert into company values(‘Religare’,’Mumbai’,8908); Insert into company values(‘Apple inc.’,’Washington’,6013); Insert into company values(‘Sun pharmaceuticals’,’Delhi’,5018); Insert into company values(‘Reliance communication’,’Bengaluru’,4506); Insert into company values(‘samsung’,’Mumbai’,1204); Insert into company values(‘Wallmart’,’Jodhpur’,5671); Insert into company values(‘Patanjali’,’Haridwar’,4603); Insert into company values(‘KFC’,’Delhi’,6018); Insert into company values(‘SP jindal & associates’,’Delhi’,8912); Insert into company values(‘Google inc.’,’chennai’,7803); Insert into company values(‘Hewlett packard’,’New york’,2015); Insert into company values(‘Vodafone’,’Bengaluru’,5671); Insert into company values(Dumtco. Transport’,’Delhi’,8172); Insert into company values(‘Dell’,’New york’,8908); Insert into company values(‘Microsoft corp.’,’Mumbai’,8858);

10.

Updating the attribute value Syntax:-update table_name set condition; Query:-update emp set salary=salary+5000;

11.

Updating the attribute value as per condition

Syntax:- update table_name set attribute=’ ‘ where condition. Query:-update company set c_city=’Gurgaon’ where c_name=’Wallmart’;

12.

Adding a new column

Syntax:- alter table table_name add column

column_name datatype(size);

Query:- alter table emp add column Designation varchar(20);

13.

Changes the datatype of attribute.

Syntax:- alter table table_name modify attribute new_datatype; Query:-alter table emp modify salary float;

14.

Dropping an attribute.

Query:- alter table emp drop column Department; Syntax:- alter table table_name drop column attribute;

15.

Deletes a row

Syntax:-delete from table_name where condition; Query:- delete from company where c_name=’Dumtco. Transport’;...