MySQL
Installing
Run this with root permission in your terminal.
apt-get install mysql-server
This will ask you for a password. Type 'password' there.
Tasks
Create a 'camp' database
Create a 'students' table in it with the fields
Name
College
District
Email
Age
Year
Semester
Team number
Branch
Insert atleast 10 records in it.
Retrieve all the students whose age is greater than 20
Retrieve all the students who are in 3rd year
Retrieve all the students who are in 4th,5th or the 6th team
Retrieve all the students who are in 2nd year, 2nd semester.
Retrieve all the students who are in ECE branch.
Retrieve all the students whose name starts with 'a' and ends with 'd'.
Create a table called 'favourites' it must have four fields
Name
Favourite Session 1
Favourite Session 2
Favourite Session 3
Now, Insert the records with the values of the 'Favourite Sessions' being any session in the camp that you like. For example I like 'python','git','communication-skills' so, I will insert ('ganesh','python','git','communication-skills').
Retrieve all the students who have 'python' as favourite 1.
Retrieve all the students who dont have 'databases' as favourite 2.
Retrieve all the students who have 'git' or 'documentation' as favourite 1.
Retrieve all the students who have 'python' as favourite 1, 'editors' as favourite 2, and 'databases' as favourite 3.
Update the email of a student in the 'students' table to '
[email protected]' where the name is 'john' (Create a row with name = john for this.)
Find the number of students in students table.
Reference
Create
Replace tablename with your table's name, the fieild1,field2 with your field's names' and the type1,type2 with your respective types.
CREATE TABLE tablename (field1 type1,field2 type2);
Select
Selecting all columns
Selecting some columns
SELECT field1,field2 FROM tablename;
Selecting with conditions
SELECT * FROM tablename WHERE FIELD == VALUE;
Supported Conditions
FIELD > NUMBER (Eg : age > 10 )
FIELD < NUMBER
FIELD == NUMBER
FIELD >= NUMBER
FIELD <= NUMBER
FIELD IN (v1,v2,v3) (Eg: name IN ('ganesh','divya'))
FIELD NOT n (v1,v2,v3)
FIELD LIKE '%' (Eg: name LIKE 'g%' [Matches ALL names starting WITH g] OR name LIKE '%g' [matches ALL names ending WITH g])
FIELD NOT LIKE '%'
Multiple constraints
We can use and,or in SQL to support multiple constraints
SELECT * FROM tablename WHERE n > 10 AND b < 20; # Executes ONLY IF BOTH a > 10 AND b < 20
SELECT * FROM tablename WHERE n > 10 OR b < 20; # Executes IF any IS TRUE
Getting Count
SELECT COUNT(*) FROM tablename;
Update
UPDATE tablename SET FIELD = VALUE WHERE field2 = value2;
Multiple constraints apply here also.
Insert
INSERT INTO tablename VALUES ( value1, value2, value3 );