BASIC SQL COMMANDS
TO CREATE A DATABASE.
CREATE DATABASE SENIOR;
CREATE DATABASE IF NOT EXISTS SENIOR_DATA;
TO OPEN A DATABASE.
USE SENIOR_DATA;
TO LIST THE TABLES.
SHOW TABLES;
TO CREATE A TABLE.
CREATE TABLE STUDENT (
R_ID INT (4) PRIMARY KEY CHECK (R_ID > 1000),
NAME VARCHAR (20) NOT NULL,
GENDER CHAR (1),
MATH INT (2) DEFAULT ‘ab’,
PHY INT (2) DEFAULT ‘ab’,
CHEM INT (2) DEFAULT ‘ab’,
TOTAL INT (3),
AVG DECIMAL (5, 2)
);
TO INSERT DATA INTO TABLE.
INSERT INTO STUDENT (R_ID ,NAME,GENDER,MATH,PHY,CHEM,TOTAL,AVG)
VALUES (1230,’DHARUN’, ’M’, 25, 18, 11, 54, 18.0);
INSERT INTO STUDENT VALUES (1234,’ARUNA’, ’F’, 20, 19, 21, 60, 20.0),
(1235,’KUMAR’, ’M’, 19, 18, 12, 52, 17.66),
(1236,’HARI’, ’M’, 20, 24, 25, 67, 22.33);
INSERT INTO STUDENT (R_ID ,NAME,MATH) VALUES (1234,’ARUNA’,22);
TO VIEW TABLE DATA.
SELECT * FROM STUDENT;
SELECT NAME, TOTAL FROM STUDENT;
TO UPDATE DATA IN THE TABLE.
UPDATE STUDENT SET PHY=22 WHERE R_ID=1236;
UPDATE STUDENT SET NAME=’SIVA’ WHERE NAME LIKE ‘HARI’;
UPDATE STUDENT SET CHEM=15 WHERE R_ID=1235 AND NAME LIKE ‘KUMAR’;
SELECT COMMAND.
SELECT * FROM STUDENT WHERE NAME LIKE ‘SIVA’;
SELECT * FROM STUDENT WHERE TOTAL>60;
SELECT NAME, AVG FROM STUDENT WHERE PHY>20 AND CHEM>20;
SELECT R_ID, NAME, TOTAL FROM STUDENT WHERE PHY BETWEEN 0 AND 13;
SELECT NAME,TOTAL FROM STUDENT WHERE PHY IN (23, 24, 25);
TO ELIMINATE REDUNDANT DATA.
SELECT DISTINCT CLASS FROM STUDENT;
SELECT DISTINCT (TOTAL) FROM STUDENT;
TO SELECT ALL THE ROWS.
SELECT ALL NAME FROM STUDENT;
TO VIEW STRUCTURE OF THE TABLE.
DESC STUDENT;
DESCRIBE STUDENT;
TO PERFORM SIMPLE CALCULATION.
SELECT 4*3;
SELECT 4*3 FROM DUAL;
SCALAR EXPRESSION WITH SELECTED FILES.
SELECT NAME, PHY*2 FROM STUDENT;
SELECT TOTAL%3 FROM STUDENT;
USING COLUMN ALIASES.
SELECT R_ID AS ’R_ID’ FROM STUDENT;
SELECT 22%7 AS PI;
SELECT NAME, TOTAL, TOTAL/3 AS AVERAGE FROM STUDENT;
HANDLING NULLS.
SELECT NAME, TOTAL, IFNULL (CLASS,’SENIOR_DATA’) FROM STUDENT;
PUTTING TEXT IN THE QUERY OUTPUT.
SELECT NAME, TOTAL*2, ‘PASS’ FROM STUDENT;
SELECT NAME,’IS PASSED AND GOT’, (TOTAL/75)*100,’%’ FROM STUDENT;
SELECTING SPECIFIC ROWS WHERE CLAUSE.
SELECT NAME, TOTAL, AVG FROM STUDENT WHERE R_ID > 1200;
SELECT * FROM STUDENT WHERE PHY < > 20;
SELECT * FROM STUDENT WHERE (PHY>20 AND CHEM>20) && GENDER=’M’;
RELATIONAL OPERATORS.
SELECT * FROM STUDENT WHERE NAME < > ‘HARI’;
SELECT * FROM STUDENT WHERE TOTAL = 52;
LOGICAL OPERATORS.
SELECT NAME, TOTAL FROM STUDENT WHERE (PHY=19 OR PHY=24);
SELECT * FROM STUDENT WHERE (CHEM=25 AND CHEM=12);
SELECT NAME FROM STUDENT WHERE (NOT AVG=20.0);
CONDITION BASED ON RANGE.
SELECT * FROM STUDENT WHERE PHY BETWEEN 15 AND 25;
CONDITION BASED ON LIST.
SELECT * FROM STUDENT WHERE AVG IN (20.0, 21.0, 22.0);
SELECT * FROM STUDENT WHERE TOTAL NOT IN (52, 70, 67);
CONDITION BASED ON PATTERN MATCHES.
SELECT * FROM STUDENT WHERE NAME LIKE ‘A%’;
SELECT * FROM STUDENT WHERE NAME NOT LIKE ‘S%’;
SELECT * FROM STUDENT WHERE NAME LIKE ‘_ _ _ _’; # EXACTLY 4 CHARACTERS
SELECT * FROM STUDENT WHERE NAME LIKE ‘_ _ _ _ R’;
SELECT * FROM STUDENT WHERE NAME LIKE ‘_ R _%’;
SEARCHING FOR NULL.
SELECT NAME FROM STUDENT WHERE MATH IS NULL;
SORTING RESULT – ORDER BY CLAUSE.
SELECT * FROM STUDENT ORDER BY TOTAL;
SELECT * FROM STUDENT WHERE AVG>19.0 ORDER BY NAME;
SELECT * FROM STUDENT ORDER BY TOTAL DESC;
SELECT * FROM STUDENT WHERE AVG>19.0 ORDER BY NAME ASC;
AGGREGATE FUNCTION.
1. AVG
SELECT AVG (TOTAL) ‘AVERAGE’ FROM STUDENT;
2. COUNT
SELECT COUNT (*) ‘NO. OF STUDENTS’ FROM STUDENT;
3. MAX
SELECT MAX (TOTAL)’MAXIMUM TOTAL’ FROM STUDENT;
4. MIN
SELECT MIN (TOTAL)’MINIMUM TOTAL’ FROM STUDENT;
5. SUM
SELECT SUM (PHY)’TOTAL IN PHY’ FROM STUDENT;
TO REMOVE DATABASES.
DROP DATABASE SENIOR_DATA;
CREATING TABLE FROM EXISTING TABLE.
CREATE TABLE SCHOOL AS (SELECT NAME, TOTAL, AVG FROM STUDENT);
INSERTING DATA FROM ANOTHER TABLE.
INSERT INTO SCHOOL SELECT * FROM STUDENT;
DELETING DATA WITH DELETE COMMAND.
DELETE FROM STUDENT WHERE TOTAL <25;
ALTER TABLE COMMAND.
ADDING COLUMNS:
ALTER TABLE STUDENT ADD (TEL_NUMBER INTEGER);
MODIFYING COLUMN DEFINITIONS:
ALTER TABLE STUDENT CHANGE AVG AVERAGE DECIMAL (5, 2);
ALTER TABLE STUDENT MODIFY (NAME VARCHAR (25));
REMOVING TABLE COMPONENTS:
ALTER TABLE STUDENT DROP NOT NULL, DROP COLUMN AVGERAGE;
DROP TABLE COMMAND.
DROP TABLE SCHOOL;
DROP TABLE IF EXISTS SCHOOL;
GROUPING RESULT-GROUP BY.
SELECT * FROM STUDENT GROUP BY NAME;
SELECT NAME, PHY FROM STUDENT GROUP BY PHY;
PLACING CONDITIONS ON GROUPS – HAVING CLAUSE.
SELECT NAME FROM STUDENT GROUP BY TOTAL HAVING TOTAL >50;
SELECT COUNT (*), PHY FROM STUDENT GROUP BY PHY HAVING COUNT (*) >1;
CARTESIAN PRODUCT.
SELECT * FROM STUDENT, SCHOOL;
EQUI-JOIN
SELECT STUDENT.R_ID, NAME, TOTAL FROM STUDENT, SCHOOL
WHERE STUDENT.R_ID = SCHOOL.ROOKIE_ID;
NATURAL JOIN
SELECT * FROM STUDENT NATURAL JOIN SCHOOL;
CONNECTING MYSQL FROM PYTHON.
import [Link] as sqltor
mycon=[Link](host=’localhost’,user=’root’,passwd=’MyPass’,database=’test’)
if mycon.is_connected()==False:
print(‘Error connecting to MySQL database’)
cursor=[Link]()
[Link](‘select * from student’)
data=[Link]()
count=[Link]
print(‘Total number of rows retrieved so far from resultset:’,count)
data=[Link]()
count=[Link]
print(‘Total number of rows retrieved so far from resultset: ’count)
data=[Link](3)
count=[Link]
print(‘Total number of rows retrieved so far from resultset: ’count)
CONNECTING MYSQL FROM PYTHON.
import [Link] as sqltor
mycon=[Link](host=’localhost’,user=’root’,passwd=’MyPass’,database=’test’)
if mycon.is_connected()==False:
print(‘Error connecting to MySQL database’)
cursor=[Link]()
[Link](‘select * from student’)
data=[Link]()
count=[Link]
print(‘Total number of rows retrieved so far from resultset:’,count)
data=[Link]()
count=[Link]
print(‘Total number of rows retrieved so far from resultset: ’count)
data=[Link](3)
count=[Link]
print(‘Total number of rows retrieved so far from resultset: ’count)
