Docstoc

SQL database query

Document Sample
SQL database query Powered By Docstoc
					create database furniture;

create table customer(cust_id int primary key, cust_name char(10), cust_add char(15),
city char(10), state char(10), postal_code int);
drop table order_line;
select * from customer;
insert into customer values(1001,"raj","shastri nagar","jodhpur","rajasthan",342001);
insert into customer values(1002,"hitesh","hanuman nagar","jaipur","rajasthan",342221);
insert into customer values(1003,"govind","gayatri nagar","jodhpur","rajasthan",342001);
insert into customer values(1004,"arvind","chb 115","ajmer","rajasthan",34555);
insert into customer values(1005,"arman","subhash nagar","delhi","",3420225);
insert into customer values(1006,"gaurav","mira road","bombay","maharastra",342655);
insert into customer values(1007,"sourabh","daryaganj","delhi","_",323565);
insert into customer values(1008,"manish","link road","jodhpur","rajsthan",345454);


create table orders(order_id int primary key, order_date datetime, cust_id int);
select * from orders;
delete from product;
insert into orders values(1101,"2/2/2010",1003);
insert into orders values(1102,"1/2/2010",1004);
insert into orders values(1103,"5/2/2010",1005);
insert into orders values(1104,"6/2/2010",1003);
insert into orders values(1105,"6/2/2010",1004);
insert into orders values(1106,"9/2/2010",1005);
insert into orders values(1108,"10/2/2010",1001);
insert into orders values(1107,"10/2/2010",1002);
insert into orders values(1109,"11/2/2010",1002);
insert into orders values(1110,"12/2/2010",1001);


create table order_line(productline_id int primary key, product_id int, order_qty int);
select * from order_line;
insert into order_line values(1105,104,2);
insert into order_line values(1106,100,5);
insert into order_line values(1105,103,6);
insert into order_line values(1102,109,8);
insert into order_line values(1102,142,6);
insert into order_line values(1109,101,5);
insert into order_line values(1111,105,1);

create table product(product_id int primary key, product_des char(20), product_finish int,
product_line_id int);
select * from product;
insert into product values (2001, "wooden dining table",2,100);
insert into product values (2002, "wooden tvc",2,103);
insert into product values (2003, "wooden study table",20,100);
insert into product values (2004, "wooden dining table",20,103);
insert into product values (2005, "wooden drawer chest",25,142);
insert into product values (2006, "wooden computer table",15,103);
insert into product values (2007, "wooden chairs",15,100);


QUERY 1

select product_id,product_finish from product;

QUERY 2

select product_id, product_des from product order by product_line_id;

QUERY 3

select count(*) from product group by product_line_id;


QUERY 4

select cust_name from customer, orders where cust_name like "a%";

query 5

select product_des, product_id from product where product_des like "%table%";

query 6

update product set product_des=tables where "wooden tvc" ;

query 7

select count(*) from orders,order_line group by order_date;
……………………
.
…




.
.
.
.
.
.
.

create database employee;

create table employee_table(emp_no int primary key, emp_name char(10), job char(10),
heir_date datetime,basic_salary int, commision int, dept_no int);
drop table dept_table;
select * from employee_table;
insert into employee_table values(1001,"rahul ","supervisor","1/1/2010",5000,500,01);
insert into employee_table values(1002,"arman ","manager","1/2/2010",8500,850,02);
insert into employee_table values(1003,"rohit ","salesman","5/1/2010",4800,480,02);
insert into employee_table values(1004,"govind ","worker","6/2/2010",4200,420,02);
insert into employee_table values(1005,"sohail ","chair
person","9/3/2010",11000,1100,01);
insert into employee_table values(1006,"nitin","salesman","9/3/2010",9000,900,01);
insert into employee_table values(1007,"arvind ","manager","9/3/2010",6500,650,01);
insert into employee_table values(1008,"hitesh ","worker","5/4/2010",3800,380,01);
insert into employee_table values(1009,"harsh","quality
controler","6/4/2010",7600,760,01);
insert into employee_table values(1010,"gaurav ","salesman","9/4/2010",6300,630,03);
insert into employee_table values(1011,"daiwat","worker","1/5/2010",4800,480,03);
insert into employee_table values(1012,"amit","salesman","1/5/2010",4950,495,03);
insert into employee_table values(1013,"ankit ","supervisor","1/6/2010",5000,500,04);
insert into employee_table values(1014,"govind ","salesman","2/6/2010",4000,400,04);
insert into employee_table values(1015,"dilip","manager","6/6/2010",8000,800,04);
insert into employee_table values(1016,"nitesh ","worker","8/6/2010",4500,450,05);
insert into employee_table values(1017,"manish","manager","9/6/2010",7000,700,05);
insert into employee_table values(1018,"harsh","supervisor","9/6/2010",8000,800,05);
insert into employee_table values(1019,"akshit ","salesman","2/8/2010",1990,199,05);
insert into employee_table values(1020,"nitesh","salesman","3/8/2010",3500,350,04);
insert into employee_table values(1021,"dilip ","salesman","3/8/2010",1900,190,03);



create table dept_table(dept_no int primary key, dept_name char(10), location char(10));
select * from dept_table;

insert into dept_table values(01,"production","jodhpur");
insert into dept_table values(02,"marketing","jaipur");
insert into dept_table values(03,"marketing","jaipur");
insert into dept_table values(04,"retailing","ajmer");
insert into dept_table values(05,"production","delhi");
create table client_master(client_no int primary key,client_name char(30),address
char(25), city char(10),pincode int,state char(12),balance_due int);
select * from client_master;
insert into client_master values(100001, "sr agency", "shastri nagar
","jodhpur",342001,"rajasthan",12400);
insert into client_master values(100002, "rl khanna comp.","daryaganj
","delhi",252063,"delhi",58500);
insert into client_master values(100003 ,"patel agency", "station road
","ajmer",346952,"rajasthan",null);
insert into client_master values(100004 ,"tms company", "5th road
","jodhpur",342001,"rajasthan",25620);
insert into client_master values(100005 ,"royal systems", "mira road
","mumbai",25698,"maharashtra",null);




create table product_master(product_no int primary key, description char(20),profit
int,qty int,sell_price int,cost_price int);
select * from product_master;
insert into product_master values (30001,"laptop-450",3500,25,45200,32000);

insert into product_master values (30001,"laptop-450",3500,25,45200,32000);
insert into product_master values (30003,"desktop-hp1325",2600,6,41200,26500);
insert into product_master values (30002,"think-pad1113",2750,15,43200,29000);
insert into product_master values (30009,"keyboard-iball",350,80,1450,890);
insert into product_master values (30008,"hp-inkjet-printer",1200,10,4560,3200);
insert into product_master values (30005,"hp-laser.printer",1560,12,6580,4520);
insert into product_master values (30004,"hp-scanner",900,16,4690,3625);
insert into product_master values (30010,"ups-aps",360,55,1900,1560);


create table salesman_master(salesman_no int primary key, salesman_name char(10),
address char(20),city char(10),state char(10),sales_amt int, target int);
select * from salesman_master;
insert into salesman_master values (50101,"rohit","jalori
gate","jodhpur","rajasthan",36000,100000);
insert into salesman_master values
(50103,"amit","ghantaghar","jodhpur","rajasthan",25000,60000);
insert into salesman_master values
(50105,"govind","boranada","jodhpur","rajasthan",15000,50000);
insert into salesman_master values (50104,"nitin","gandhi
marg","jaipur","rajasthan",56000,90000);
insert into salesman_master values (50102,"gaurav","mi
road","surat","gujarat",25300,60000);
insert into salesman_master values (50106,"akshit","chandni chaowk","gandhi
dham","gujarat",26000,70000);
insert into salesman_master values (50108,"nitesh","subhash
marg","nagaur","rajasthan",23000,70000);
insert into salesman_master values (50107,"dilip","chandni chaowk","gandhi
dham","gujarat",26000,70000);


q1

select emp_name from employee_table where basic_salary<5000;
q2
select job,emp_name from employee_table,dept_table where emp_name like'[b-r]%';
q3
select dept_name from dept_table where location="jaipur" or location="jodhpur";
q4
select emp_name from employee_table where dept_no=3 or dept_no=2;
q5
select job,basic_salary from employee_table where job="salesman" or job="manager"
and basic_salary>4000;
q6
select emp_name,heir_date from employee_table order by heir_date;
q7
delete from salesman_master where in (select from employee_table where
basic_salary<2000);
q8
delete from product_master where qty<20;




create database employee2;
drop table
create table employee_table(emp_no int primary key, emp_name char(10), job char(10),
heir_date datetime,basic_salary int, commision int, dept_no int);
drop table employee_table;
select * from employee_table;
insert into employee_table values(01,'rahul ','supervisor','1/1/2010',5000,500,01);
insert into employee_table values(02,'arman ','manager','1/2/2010',8500,850,02);
insert into employee_table values(10,'rohit ','salesman','5/1/2010',4800,480,03);
insert into employee_table values(12,'govind ','worker','6/2/2010',4200,420,11);
insert into employee_table values(11,'sohail ','chair person','9/3/2010',11000,1100,02);
insert into employee_table values(03,'nitin','salesman','9/3/2010',9000,900,01);
insert into employee_table values(09,'arvind ','manager','9/3/2010',6500,650,01);
insert into employee_table values(04,'hitesh ','worker','5/4/2010',3800,380,01);
insert into employee_table values(05,'harsh','quality controler','6/4/2010',7600,760,04);




create table dept_table(dept_no int primary key, dept_name char(10), location char(10));
select * from dept_table;
drop table dept_table;
insert into dept_table values(01,'production','jodhpur');
insert into dept_table values(02,'marketing','jaipur');
insert into dept_table values(03,'marketing1','jaipur');
insert into dept_table values(04,'retailing','ajmer');
insert into dept_table values(05,'product-1','delhi');

q1- equi-join
select e.emp_name,d.dept_name from employee_table e, dept_table d where
e.dept_no=d.dept_no;

rahul          production
arman          marketing
nitin          production
hitesh         production
arvind         production
rohit          marketing1

q2- left join
select e.emp_name,d.dept_name from employee_table e left join dept_table d on
e.dept_no=d.dept_no;

rahul          production
arman          marketing
nitin         production
hitesh        production
arvind        production
rohit         marketing1
govind        NULL

q3-right join
select e.emp_name,d.dept_name from employee_table e right join dept_table d on
e.dept_no=d.dept_no;

rahul        production
nitin        production
hitesh       production
arvind       production
arman        marketing
rohit        marketing1
NULL retailing
NULL product-1

q4-self join
select e.emp_name from employee_table e join employee_table f on
e.emp_no=f.dept_no;

rahul
arman
rahul
rahul
rahul
nitin

q5-
select emp_name,job,dept_name from employee_table e,dept_table d where e.dept_no=
d.dept_no and d.dept_name='production';

rahul         supervisor    production
nitin         salesman      production
hitesh        worker        production
arvind        manager       production

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:17
posted:4/3/2012
language:
pages:7
Description: SQL database query,SQL,SQL introduction,MY SQL