User Tools

Site Tools


databases

Table of Contents

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

  1. Create a 'camp' database
  2. Create a 'students' table in it with the fields
    1. Name
    2. College
    3. District
    4. Email
    5. Age
    6. Year
    7. Semester
    8. Team number
    9. Branch
  3. Insert atleast 10 records in it.
  4. Retrieve all the students whose age is greater than 20
  5. Retrieve all the students who are in 3rd year
  6. Retrieve all the students who are in 4th,5th or the 6th team
  7. Retrieve all the students who are in 2nd year, 2nd semester.
  8. Retrieve all the students who are in ECE branch.
  9. Retrieve all the students whose name starts with 'a' and ends with 'd'.
  10. Create a table called 'favourites' it must have four fields
    1. Name
    2. Favourite Session 1
    3. Favourite Session 2
    4. Favourite Session 3
  11. 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').
  12. Retrieve all the students who have 'python' as favourite 1.
  13. Retrieve all the students who dont have 'databases' as favourite 2.
  14. Retrieve all the students who have 'git' or 'documentation' as favourite 1.
  15. Retrieve all the students who have 'python' as favourite 1, 'editors' as favourite 2, and 'databases' as favourite 3.
  16. Update the email of a student in the 'students' table to 'xyz@google.com' where the name is 'john' (Create a row with name = john for this.)
  17. 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
SELECT * FROM tablename;
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 );
databases.txt · Last modified: 2018/03/24 11:13 (external edit)