INSERT INTO student (sid, fname, lname, town) VALUES(1, ”Julian”, ”Blackthorn”, “New York” )
Insert records into a table
INSERT INTO student VALUES(2, “Emma”, “Carstairs”, “New York”)
Insert records into a table
SELECT * FROM student; Display all the records of the table SELECT DISTINCT town FROM student; Display all the towns in the table without repeating SELECT * FROM student LIMIT 3; Display the first 3 records in the table SELECT * FROM student LIMIT 2,3; Display 3 records starting from the 2nd record
SELECT fname,town FROM student ORDER BY sid ASC;
Display fname and town records in the ascending order of the sid
SELECT * FROM student WHERE town =”New York”;
Display all the records of the students who live in New York
SELECT * FROM student WHERE sid>=11;
Display all the records of the students whose sid is greater than or equal to 11
SELECT * FROM student WHERE town=”New York” AND sid>5;
Display the records of the students who live in New York and whose sid is greater than 5
SELECT * FROM student WHERE town=”New York” OR sid>5;
Display the records of the students who live in New York or whose sid is greater than 5
SELECT * FROM student WHERE NOT town=”New York” ;
Display records of the students who don’t live in New York
SELECT * FROM srudent WHERE Sid BETWEEN 1 AND 7;
Display the records of the students whose sid is between 1 and 7
SELECT * FROM srudent WHERE town=”New York” OR “Manhattan”;
Display the records of the students who lives in New York or Manhattan
SELECT * FROM srudent WHERE town IN (”New York”, “Manhattan”);
Display the records of the students who lives in New York or Manhattan
SELECT * FROM srudent WHERE town NOT IN (”New York”, “Manhattan”);
Display the records of the students who don’t live in New York or Manhattan
SELECT * FROM student WHERE town LIKE “_an%”;
Display the records of the students whose town name’s second and third letters are “an” and there can be any number of letters afterwards.
SELECT CONCAT (fname, ” ”, lname) FROM student:
Display fname and lname together with a space between
SELECT UPPER (fname) AS Name FROM student Display the fname of students in uppercase with the label Name
SELECT MIN(sid) AS First Index FROM student; Display the smallest value in sid field with the label First Index
SELECT MAX(sid) AS Last Index FROM student; Display the largest value in sid field with the label Last Index
SELECT SUM(fee) AS Total FROM course; Display the total of the fee field with the label Total
SELECT AVG (fee) AS Average FROM course; Display the average of the fee field with the label Average
SELECT COUNT (sid) AS Number FROM (student); Display the number of students with label Number
UPDATE student SET town=”LA” WHERE sid=1; Change town of the student whose sid is 1, to
LA DELETE FROM student WHERE sid=1; Erase the record of the student whose sid is 1
DELETE FROM student; Erase all the records of the table SELECT student.fname , enroll.date FROM students
INNER JOIN enroll ON student.sid=enroll.sid; Join 2 tables (inner join) and creates a temporary file
