SQL example



1. CLICK HERE TO RUN SQL COMMANDS 
2. CLICK HERE TO RUN SQL COMMANDS
3. CLICK HERE TO RUN JOIN QUERIES

Click Here to download SQL Notes

____________________________________________________________________________________
DROP TABLE IF EXISTS contacts;  
CREATE TABLE contacts (  
id INT PRIMARY KEY,  
first_name VARCHAR(30) NOT NULL,  
last_name VARCHAR(25) NOT NULL,   
    email VARCHAR(210) NOT NULL,  
    age VARCHAR(22) NOT NULL);  
   INSERT INTO contacts VALUES (1,'Kavin','Peterson','kavin.peterson@verizon.net','21');  
       INSERT INTO contacts VALUES(2,'Nick','Jonas','nick.jonas@me.com','18');
       INSERT INTO contacts VALUES(3,'Peter','Heaven','peter.heaven@google.com','23'); 
       INSERT INTO contacts VALUES(4,'Michal','Jackson','michal.jackson@aol.com','22');
       INSERT INTO contacts VALUES(5,'Sean','Bean','sean.bean@yahoo.com','23');
       INSERT INTO contacts VALUES(6,'Tom ','Baker','tom.baker@aol.com','20');
       INSERT INTO contacts VALUES(7,'Ben','Barnes','ben.barnes@comcast.net','17');
       INSERT INTO contacts VALUES(8,'Mischa ','Barton','mischa.barton@att.net','18');
       INSERT INTO contacts VALUES(9,'Sean','Bean','sean.bean@yahoo.com','16');
       INSERT INTO contacts VALUES(10,'Eliza','Bennett','eliza.bennett@yahoo.com','25');
       INSERT INTO contacts VALUES(11,'Michal','Krane','michal.Krane@me.com','25');
       INSERT INTO contacts VALUES(12,'Peter','Heaven','peter.heaven@google.com','20');
       INSERT INTO contacts VALUES(13, 'Brian','Blessed','brian.blessed@yahoo.com','20');  
  
SELECT * FROM contacts  
ORDER BY id;  

/*find duplicate values*/
SELECT  
    email, COUNT(email)  
FROM  
    contacts  
GROUP BY  
    email  
HAVING  
COUNT (email) > 1;  
_________________________________________________________________________________
DROP TABLE IF EXISTS student;
CREATE TABLE student( id          integer primary key,  name    text,
                          branch text,     date_of_reg    date,     percentage  float,
                          sem    year);

  INSERT INTO student VALUES (1602053001,'A','CSE','2018-01-03',5.6,5);
  INSERT INTO student VALUES (1602053002,'B','EC','2018-01-04',7.5,5);
  INSERT INTO student VALUES (1602053003,'C','EE','2018-01-06',5.8,5);
  INSERT INTO student VALUES (1602053004,'D','CSE','2018-01-03',5.5,5);
  INSERT INTO student VALUES (1602053005,'E','SE','2018-01-03',8.5,5);
  INSERT INTO student VALUES (1602053006,'F','CSE',null,5.4,5);
  INSERT INTO student VALUES (1602053007,'G','CE','2018-01-07',6.5,5);
  INSERT INTO student VALUES (1602053008,'H','CSE','2018-01-03',5.9,5);
  INSERT INTO student VALUES (1602053009,'I','CE','2018-01-08',7.5,5);
  INSERT INTO student VALUES (1602053010,'J','CE','2018-01-03',5.0,5);
  INSERT INTO student VALUES (1602053011,'K',null,'2018-01-03',5.5,5);
  INSERT INTO student VALUES (1602053012,'L','ME','2018-01-09',8.5,5);
  INSERT INTO student VALUES (1602053013,'M','CSE','2018-01-03',5.2,5);
  INSERT INTO student VALUES (1602053014,'N','ME','2018-01-03',4.5,5);
  INSERT INTO student VALUES (1602053015,'O','CSE','2018-01-03',5.1,5);
  INSERT INTO student VALUES (1602053016,'P','CSE','2018-01-03',5.6,5);
  INSERT INTO student VALUES (1602053017,'Q','ME','2018-01-09',7.5,5);
  INSERT INTO student VALUES (1602053018,'R','CSE','2018-01-03',5.8,5);
  INSERT INTO student VALUES (1602053019,'S','CSE','2018-01-03',5.4,5);
  INSERT INTO student VALUES (1602053020,'T','EE','2018-01-02',5.0,5);
  INSERT INTO student VALUES (1602053021,'P','CSE','2018-01-03',5.3,5);
  INSERT INTO student VALUES (1602053022,'C','EE','2018-01-06',3.5,5);

/* 1.Select record of all students from student table*/
SELECT * FROM student;

/* 2.Select id,name from student table*/
SELECT id,name FROM student;

/* 3.Select distinct id,name from student table*/
SELECT distinct id,name FROM student;

/* 4.Count distinct id,name from student table*/
SELECT COUNT(DISTINCT id) FROM student;

/* 5.Select all students from student table where date_of_reg='2018-01-03'*/
SELECT * FROM student WHERE date_of_reg='2018-01-03';

/* 6.Select DISTINCT id,NAME,DATE_OF_REG from student table where date_of_reg='2018-01-03'*/
SELECT DISTINCT id,NAME,DATE_OF_REG FROM student WHERE date_of_reg='2018-01-03';

/* 7.Select all students from student table where BRANCH='CSE'AND DATE_OF_REG='2018-01-03'AND PERCENTAGE=5.6*/
SELECT * FROM STUDENT WHERE BRANCH='CSE'AND DATE_OF_REG='2018-01-03'AND PERCENTAGE=5.6;

/* 8.Select all students from student table where BRANCH='CSE'OR DATE_OF_REG='2018-01-03' OR PERCENTAGE=5.6*/
SELECT * FROM STUDENT WHERE BRANCH='CSE'OR DATE_OF_REG='2018-01-03' OR PERCENTAGE=5.6;

/* 9.Select all students from student table where not BRANCH='CSE'OR DATE_OF_REG='2018-01-03' OR PERCENTAGE=5.6*/
SELECT * FROM STUDENT WHERE not (BRANCH='CSE'OR DATE_OF_REG='2018-01-03' OR PERCENTAGE=5.6);

/* 10.Select all students from student table ordered by branch name*/
SELECT * FROM STUDENT order by branch ;

/* 11.Select all students from student table ordered by branch name in descending order*/
SELECT * FROM STUDENT order by branch desc;

/* 12.Select all students from student table ordered by date_of_reg  in ascending order*/
SELECT * FROM STUDENT order by date_of_reg asc;

/* 13.Insert values student table and display them*/
INSERT INTO student VALUES (1602053100,'Lateral Entry','CSE','2018-01-03',5.6,5);

SELECT * FROM student;

/* 14.Insert values student table where id is not null and display them*/
INSERT INTO student VALUES (1602053101,'Lateral Entry2',null ,'2018-01-03',5.6,5);

SELECT * FROM student;

/* 15.Select null values from student table */
SELECT * FROM student where branch is null;

/* 16.Select null values from student table */
SELECT * FROM student where branch is not null;

/* 17.Update the record of roll number 1602053100 from student table */
update student set name='changed' , branch='CSE' where id=1602053100;
SELECT * FROM student;

/* 18.Delete the record of roll number 1602053100 from student table */
delete from student where id=1602053100;
SELECT * FROM student;

____________________________________________________________________________________
DROP TABLE IF EXISTS student;
CREATE TABLE student( id          integer,  name    text,
                          branch text,     date_of_reg    date,     percentage  float,
                          sem    year);

  INSERT INTO student VALUES (1602053001,'A','CSE','2018-01-03',5.6,5);
  INSERT INTO student VALUES (1602053002,'B','EC','2018-01-04',7.5,5);
  INSERT INTO student VALUES (1602053003,'C','EE','2018-01-06',5.8,5);
  INSERT INTO student VALUES (1602053004,'D','CSE','2018-01-03',5.5,5);
  INSERT INTO student VALUES (1602053005,'E','SE','2018-01-03',8.5,5);
  INSERT INTO student VALUES (1602053006,'F','CSE','2018-01-03',5.4,5);
  INSERT INTO student VALUES (1602053007,'G','CE','2018-01-07',6.5,5);
  INSERT INTO student VALUES (1602053008,'H','CSE','2018-01-03',5.9,5);
  INSERT INTO student VALUES (1602053009,'I','CE','2018-01-08',7.5,5);
  INSERT INTO student VALUES (1602053010,'J','CE','2018-01-03',5.0,5);
  INSERT INTO student VALUES (1602053011,'K','CSE','2018-01-03',5.5,5);
  INSERT INTO student VALUES (1602053012,'L','ME','2018-01-09',8.5,5);
  INSERT INTO student VALUES (1602053013,'M','CSE','2018-01-03',5.2,5);
  INSERT INTO student VALUES (1602053014,'N','ME','2018-01-03',4.5,5);
  INSERT INTO student VALUES (1602053015,'O','CSE','2018-01-03',5.1,5);
  INSERT INTO student VALUES (1602053016,'P','CSE','2018-01-03',5.6,5);
  INSERT INTO student VALUES (1602053017,'Q','ME','2018-01-09',7.5,5);
  INSERT INTO student VALUES (1602053018,'R','CSE','2018-01-03',5.8,5);
  INSERT INTO student VALUES (1602053019,'S','CSE','2018-01-03',5.4,5);
  INSERT INTO student VALUES (1602053020,'T','EE','2018-01-02',5.0,5);
  INSERT INTO student VALUES (1602053021,'P','CSE','2018-01-03',5.3,5);
  INSERT INTO student VALUES (1602053022,'C','EE','2018-01-06',3.5,5);


SELECT * FROM student;
SELECT DISTINCT branch,percentage FROM student;
SELECT DISTINCT name,percentage FROM student;
SELECT COUNT (DISTINCT id) FROM student;
SELECT COUNT (DISTINCT name) FROM student;
SELECT * FROM student WHERE date_of_reg='2018-01-03';
SELECT DISTINCT id,NAME,DATE_OF_REG FROM student WHERE date_of_reg='2018-01-03';
SELECT * FROM STUDENT WHERE BRANCH='CSE'AND DATE_OF_REG='2018-01-03'AND PERCENTAGE=5.6;
SELECT * FROM STUDENT WHERE BRANCH='CSE'OR DATE_OF_REG='2018-01-03' OR PERCENTAGE=5.6;
SELECT  ID,NAME FROM STUDENT ORDER BY ID ASC;
SELECT  ID,NAME FROM STUDENT ORDER BY ID DESC;
_____________________________________________________________________________________
OUTPUT
idnamebranchdate_of_regpercentagesem
1602053001ACSE2018-01-035.65
1602053002BEC2018-01-047.55
1602053003CEE2018-01-065.85
1602053004DCSE2018-01-035.55
1602053005ESE2018-01-038.55
1602053006FCSE2018-01-035.45
1602053007GCE2018-01-076.55
1602053008HCSE2018-01-035.95
1602053009ICE2018-01-087.55
1602053010JCE2018-01-0355
1602053011KCSE2018-01-035.55
1602053012LME2018-01-098.55
1602053013MCSE2018-01-035.25
1602053014NME2018-01-034.55
1602053015OCSE2018-01-035.15
1602053016PCSE2018-01-035.65
1602053017QME2018-01-097.55
1602053018RCSE2018-01-035.85
1602053019SCSE2018-01-035.45
1602053020TEE2018-01-0255
1602053021PCSE2018-01-035.35
1602053022CEE2018-01-063.55
branchpercentage
CSE5.6
EC7.5
EE5.8
CSE5.5
SE8.5
CSE5.4
CE6.5
CSE5.9
CE7.5
CE5
ME8.5
CSE5.2
ME4.5
CSE5.1
ME7.5
CSE5.8
EE5
CSE5.3
EE3.5
namepercentage
A5.6
B7.5
C5.8
D5.5
E8.5
F5.4
G6.5
H5.9
I7.5
J5
K5.5
L8.5
M5.2
N4.5
O5.1
P5.6
Q7.5
R5.8
S5.4
T5
P5.3
C3.5
COUNT (DISTINCT id)
22
COUNT (DISTINCT name)
20
idnamebranchdate_of_regpercentagesem
1602053001ACSE2018-01-035.65
1602053004DCSE2018-01-035.55
1602053005ESE2018-01-038.55
1602053006FCSE2018-01-035.45
1602053008HCSE2018-01-035.95
1602053010JCE2018-01-0355
1602053011KCSE2018-01-035.55
1602053013MCSE2018-01-035.25
1602053014NME2018-01-034.55
1602053015OCSE2018-01-035.15
1602053016PCSE2018-01-035.65
1602053018RCSE2018-01-035.85
1602053019SCSE2018-01-035.45
1602053021PCSE2018-01-035.35
idnamedate_of_reg
1602053001A2018-01-03
1602053004D2018-01-03
1602053005E2018-01-03
1602053006F2018-01-03
1602053008H2018-01-03
1602053010J2018-01-03
1602053011K2018-01-03
1602053013M2018-01-03
1602053014N2018-01-03
1602053015O2018-01-03
1602053016P2018-01-03
1602053018R2018-01-03
1602053019S2018-01-03
1602053021P2018-01-03
idnamebranchdate_of_regpercentagesem
1602053001ACSE2018-01-035.65
1602053016PCSE2018-01-035.65
idnamebranchdate_of_regpercentagesem
1602053001ACSE2018-01-035.65
1602053004DCSE2018-01-035.55
1602053005ESE2018-01-038.55
1602053006FCSE2018-01-035.45
1602053008HCSE2018-01-035.95
1602053010JCE2018-01-0355
1602053011KCSE2018-01-035.55
1602053013MCSE2018-01-035.25
1602053014NME2018-01-034.55
1602053015OCSE2018-01-035.15
1602053016PCSE2018-01-035.65
1602053018RCSE2018-01-035.85
1602053019SCSE2018-01-035.45
1602053021PCSE2018-01-035.35
idname
1602053001A
1602053002B
1602053003C
1602053004D
1602053005E
1602053006F
1602053007G
1602053008H
1602053009I
1602053010J
1602053011K
1602053012L
1602053013M
1602053014N
1602053015O
1602053016P
1602053017Q
1602053018R
1602053019S
1602053020T
1602053021P
1602053022C
idname
1602053022C
1602053021P
1602053020T
1602053019S
1602053018R
1602053017Q
1602053016P
1602053015O
1602053014N
1602053013M
1602053012L
1602053011K
1602053010J
1602053009I
1602053008H
1602053007G
1602053006F
1602053005E
1602053004D
1602053003C
1602053002B
1602053001A


No comments:

Post a Comment

Subscribe to: Comments (Atom)