To show all the database names: SHOW DATABASES;
TO OPEN A DATABASE => USE <DATABASE NAME>; USE SCHOOL;
TO DISPLAY ALL THE TABLE NAMES => SHOW TABLES;
TO DISPLAY THE STRUCTURE OF A TABLE STUDENT; => DESC STUDENT;
TO DISPLAY ALL THE RECORDS AND ALL THE COLUMNS OF THE TABLE STUDENT;
SELECT * FROM STUDENT;
TO DISPLAY SPECIFIED COLUMNS; SELECT ROLL, NAME, MARKS FROM STUDENT;
TO DISPLAY SPECIFIED ROWS USING A CONDITION;
SELECT * FROM STUDENT WHERE MARKS > 80;
SELECT NAME, AGE FROM STUDENT WHERE AGE BETWEEN 14 AND 16;
SELECT NAME, AGE FROM STUDENT WHERE AGE >=14 AND AGE<=16;
SELECT NAME, CITY FROM MEMBERS WHERE CITY IN (‘KOLKATA’, ‘MUMBAI’, ‘CHENNAI’);
SELECT * FROM MEMBERS WHERE CITY NOT IN (‘KOLKATA’, ‘MUMBAI’, ‘CHENNAI’);
SELECT * FROM STUDENT WHERE GENDER=’F’ AND STREAM=’MEDICAL’;
SELECT NAME, CITY FROM MEBERS WHERE PINCODE LIKE “%04”;
SELECT * FROM STUDENT WHERE FNAME LIKE “_ _ _ _”;
SELECT NAME, MARKS FROM STUDENT OREDER BY MARKS DESC;
SELECT * FROM STUDENT WHERE AGE>16 ORDER BY DOB ASC;
SELECT CITY, COUNT(*) FROM MEMBERS GROUP BY CITY;
CITY COUNT(*)
KOLKATA 5
MUMBAI 10
CHENNAI 7
SELECT CLASS, MAX(MARKS), MIN(MARKS) FROM STUDENT GROUP BY CLASS HAVING CLASS = ‘XIIH’;
SELECT DEPT, SUM(SALE), AVG(SALE) FROM EMP GROUP BY DEPT HAVING COUNT(*) > 5;
AGE
14
15
14
16
17
13
15
SELECT SUM(AGE) => 94
SELECT COUNT(AGE)=>7
TO INSERT A NEW ROW IN THE TABLE FITNESS WITH THE FOLOWING DETAILS:
“P7”, ”VIBRO EXERCISER”, 28000, “AONE”
INSERT INTO FITNESS VALUES (“P7”, ”VIBRO EXERCISER”, 28000, “AONE”);
TO CHANGE THE PRICE OF ALL THE PRODUCTS BY REDUCING THE PRICE BY 25%.
UPDATE ITEM SET PRICE = PRICE – PRICE*25/100;
TO CHANGE THE BRANDNAME TO “FIT INDIA” FOR THOSE ITEMS WHERE ICODE IS “G101”;
UPDATE ITEM SET BRANDNAME = “FIT INDIA” WHERE ICODE=”G101”;
TO DELETE THOSE EMPLOYEES RECORD WHOSE DATEOFJOINING IS BEFORE ’01-JAN-2000’;
DELETE FROM EMPLOYEE WHERE DATEOFJOINING < ’01-JAN-2000’;
TO DELETE ALL THE RECORDS => DELETE FROM EMPLOYEES;
TO DELETE THE TABLE => DROP TABLE EMPLOYEE;
SQL FUNCTIONS
1. LOWER()/LCASE() SELECT LCASE(NAME) FROM EMP;
2. UPPER()/UCASE() SELECT UPPER(NAME) FROM STUD;
3. LENGTH() SELECT LENGTH(“COMPUTER”);
4. RIGHT() SELECT RIGHT(“COMPUTER”,3);
SELECT LCASE(RIGHT(“COMPUTER”,3)); ter
SELECT UPPER(LEFT(“environment”,5)); ENVIR
5. SUBSTR() SELECT SUBSTR(‘ABCDEFG’,3,4); CDEF
SELECT SUBSTR(‘ABCDEFG’, -4, 3); DEF
6. INSTR() SELECT INSTR(‘CORPORATE’, ‘OR’); 2
SELECT INSTR(‘CORPORATE’, ‘ATE’); 7
7. TRIM
8. LTRIM SELECT LTRIM(“ COMPUTER “);
9. RTRIM
10. CONCAT() SELECT CONCAT(“COMPU”,”TER”);
11. MID() SELECT MID(“COMPUTER”,3,4); MPUT
MOD() : IT WILL RETURN THE REMAINDER OF A DIVISION
SELECT MOD(5,4);
POWER()/POW() : SELECT POWER(2,5); 32
SQRT(): SELECT SQRT(4) 2
ROUND(): SELECT ROUND(15.193,2) 15.19
SELECT ROUND(15.193,1); 15.2
SELECT ROUND(15.197,2); 15.20
CURDATE()/CURRENT_DATE(): IT RETURNS THE CURRENT
DATE.
DATE(): SELECT DATE(‘2024-10-22 [Link]’);
2024-10-22
SELECT MONTH(‘2024-10-22’); 10
SELECT MONTHNAME(‘2024-10-22’);
SELECT DAY(‘2024-10-22’); 22
SELECT DAYNAME(‘2024-10-22’); TUESDAY
SELECT YEAR(‘2024-10-22’); 2024
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
NOW()
SYSDATE()
Create database school;
Show databases;
Use school;
Show tables;
Create table twelveD
(roll integer,
Name varchar(20),
Fees decimal(7,2));
Insert into twelved
Values(1,”aman”,34500);
Select * from twelved;
Desc twelved;
Select * from twelved order by name;
Select * from twelved order by name desc;
Select name,fees from twelved where fees>40000;
][poiuyhtgfdAlter table twelved
Add(gender char(1), marks integer);
