SQL PROGRAMMING

1.CREATE DATABASE adse;

2.DROP DATABASE adse;

3.CREATE TABLE admission(
 name VARCHAR(25),
 class INT(10),
 roll INT(5)
); 

4.INSERT INTO admission(name,class,roll)
VALUES("saurav kumar",12,10);

5.SELECT adm_no FROM admission;

6.SELECT adm_no,student_name FROM admission;

7.SELECT * FROM admission;

8.UPDATE admission
SET students_name = "gautam kumar";

9.UPDATE admission
SET students_name = "gautam kumar", roll_no =2;

10.UPDATE admission
SET students_name = "priya"
WHERE adm_no = 2;

11.SELECT * FROM admission
WHERE adm_no = "2";

12.DELETE FROM admission
WHERE adm_no ="2";

13.DELETE FROM admission;

14.SELECT * FROM admission
LIMIT 2;

15.SELECT * FROM admission
ORDER BY adm_no DESC;

16.SELECT * FROM admission
ORDER BY students_name  ASC;

17.SELECT * FROM admission
ORDER BY adm_no  DESC
LIMIT 3;

18.SELECT * FROM admission
ORDER BY adm_no  DESC
LIMIT 1;

19.SELECT adm_no FROM admission
ORDER BY adm_no  DESC
LIMIT 1;

20.SELECT SUM(dues)
FROM admission;

21.SELECT COUNT(students_name)
FROM admission;

22.SELECT * FROM product_list
WHERE price = "40000";

23.SELECT * FROM product_list
WHERE price > "30000";

24.SELECT * FROM product_list
WHERE price <> "40000";

25.SELECT * FROM product_list
WHERE price BETWEEN 30000 AND 50000;

26.SELECT * FROM product_list
WHERE price IN(30000,40000,50000);

27.SELECT * FROM students
WHERE name = "ram" AND class = "2"; 

28.SELECT * FROM students
WHERE name = "ram" OR name = "shyam"; 

29.SELECT * FROM students
WHERE  NOT class = "1"; 

30.SELECT DISTINCT name FROM students;

31.UPDATE testing
SET id = "4"
WHERE NOT id = "1";

32.SELECT * FROM product_list
WHERE product_name LIKE "d%";

33.SELECT * FROM product_list
WHERE product_name LIKE "%d";

34.SELECT * FROM product_list
WHERE product_name LIKE "%a%";

35.SELECT * FROM product_list
WHERE product_name LIKE "_e%";

36.SELECT * FROM product_list
WHERE product_name LIKE "l%o";

37.CREATE TABLE demo(
adm_no INT(10),
name VARCHAR(50)    
);

38.INSERT INTO demo(name)
VALUES("saurav");

39.CREATE TABLE demo(
adm_no INT(6) NOT NULL,
name VARCHAR(50)
);

40.INSERT INTO demo(name)
VALUES("saurav");

41.CREATE TABLE demo(
adm_no INT(6) NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
PRIMARY KEY(adm_no)
);

42.INSERT INTO demo(name)
VALUES("saurav");

43.INSERT INTO demo(name)
VALUES("gautam");

44.ALTER TABLE demo
ADD COLUMN roll_no INT(6);

45.ALTER TABLE demo
DROP COLUMN name;

46.ALTER TABLE demo
MODIFY COLUMN roll_no VARCHAR(50);

47.SELECT *,
CASE
WHEN marks < 100 THEN "fail"
WHEN marks BETWEEN 100 AND 200 THEN "third"
WHEN marks BETWEEN 200 AND 300 THEN "second"
WHEN marks BETWEEN 300 AND 500 THEN "first"
ELSE "something went wrong"
END AS division
FROM result;

48.SELECT s_name,MIN(marks) AS poor_student
FROM result;

49.SELECT s_name,MAX(marks) AS amazing_student
FROM result;

50.DROP TABLE admission,payment;

51.SELECT CONCAT(name,', ',town,', ',dis,', ',state,', ',country,' ',pin_code) AS address
FROM data; 

52.SELECT name
FROM data
WHERE state = "bihar";

53.SELECT name
FROM data
WHERE state IN("bihar","karnatka");

54.SELECT *
FROM data
WHERE state IN("bihar","karnatka");

55.SELECT *
FROM data
WHERE state NOT IN("bihar","karnatka");

56.INSERT INTO admission(s_name)
SELECT name FROM data;

57.INSERT INTO admission(s_name)
SELECT name FROM data
WHERE state IN("karnatka","up");

58.CREATE TABLE america
AS 
SELECT * FROM india;

59.CREATE TABLE america(
state VARCHAR(50)    
);

60.INSERT INTO america(state)
VALUES("california"),("new york");

61.SELECT * FROM america
UNION 
SELECT * FROM india;

62.INSERT INTO(state)
VALUES("karnatka");

63.SELECT * FROM america
UNION
SELECT * FROM india;

64.SELECT * FROM america
UNION ALL
SELECT * FROM india;

65.SELECT admission.name,fee.fee
FROM admission
INNER JOIN fee
ON admission.adm_no = fee.adm_no;

66.SELECT admission.name,fee.fee
FROM admission
LEFT JOIN fee
ON admission.adm_no = fee.adm_no;

67.SELECT admission.name,fee.fee
FROM admission
RIGHT JOIN fee
ON admission.adm_no = fee.adm_no;

68.SELECT admission.name,fee.fee
FROM admission
FULL OUTER JOIN fee
ON admission.adm_no = fee.adm_no;

69.SELECT a.mem_name AS member_name,b.mem_name AS related_id
FROM members AS a
INNER JOIN members AS b
ON a.mem_id = b.rel_id;

70.SELECT course
FROM classes
GROUP BY course;

71.SELECT COUNT(adm_no),course
FROM admission
GROUP BY course;

72.SELECT COUNT(s_name),course
FROM admission
GROUP BY course;

73.SELECT COUNT(adm_no),course
FROM admission
GROUP BY course
HAVING COUNT(adm_no) < 6

74.SELECT COUNT(adm_no),course
FROM admission
GROUP BY course
HAVING COUNT(adm_no) > 6

75.CREATE TABLE a(
s_name VARCHAR(50)    
);

CREATE TABLE b(
s_name VARCHAR(50)    
);

CREATE TABLE c(
s_name VARCHAR(50)    
);

CREATE TABLE d(
s_name VARCHAR(50)    
);

76.INSERT INTO a(s_name)
VALUES("saurav");

INSERT INTO a(s_name)
VALUES("gaurav");

77.CREATE TABLE adse(
adm_no INT(6) NOT NULL AUTO_INCREMENT,
s_name VARCHAR(50),
roll_no INT(6),
PRIMARY KEY(adm_no),
UNIQUE(s_name),
UNIQUE(roll_no)    
);

78.INSERT INTO adse(s_name,roll_no)
VALUES("saurav",1);

79.INSERT INTO adse(s_name,roll_no)
VALUES("gaurav",2);

80.CREATE TABLE fee(
adm_no INT(6),
fee VARCHAR(50),
FOREIGN KEY(adm_no) REFERENCES admission(adm_no)   
);

81.INSERT INTO fee(adm_no,fee)
VALUES(1,500);

82.CREATE TABLE final(
adm_no INT(6),
CHECK(adm_no=1)
);

83.CREATE TABLE hello(
s_no INT(6),
s_name VARCHAR(50) DEFAULT 'saurav'    
);

84.INSERT INTO hello(s_no)
VALUES(1);