Learn MySql in 3 Hours

MySQL Database

CONTENTS

v CREATING A DATABASE

v CREATING A TABLE

v INSERT INTO TABLE

v DELETE FROM TABLE

v UPDATE DATA IN TABLE

v ALTER TABLE TO ADD NEW COLUMN

v EXERCISE 1

v EXERCISE 2

v EXERCISE 3

Open command prompt in windows or terminal in linux and follow with steps
mysql -u root -p
Enter password:*****

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

if you have not set any password at the time of installation use these credentials:

User Name – root
Password – (blank)

CREATING A DATABASE

Creating a database in MYSQL is simple. To start with, use the SHOW statement to find out what databases currently exist on the server.

SYNTAX                                                                                         
mysql-> SHOW DATABASES;

The mysql database describes user access privileges. The test database often is available as a workspace for users to try things out. The list of databases displayed by the statement may be different on your machine. SHOW DATABASES does not show databases that you have no privileges for if you do not have the SHOW DATABASES privilege.

Mysql-> CREATE DATABASE name;                                             

Let us create a new database with the name exercise1.
Mysql-> CREATE DATABASE exercise1;
        Now you can notice that the newly created database "Exercise1" have been added to the database currently existing on the system. Before writing the queries, you need to check whether your database has been selected. If not you need to select your database Exercise1.This can be done by using USE statement. The USE statement does not require semicolon.

Exercise1

Mysql-> USE Exercise1
Database changed

CREATING A TABLE

To Create table in the selected database edit the following query:         

            CREATE TABLE tour_guides( Employee_number INT NOT NULL,
                                         Employee_name VARCHAR (100),
                                         Hourly_rate INT,
                                         PRIMARY KEY (Employee_number));
• Here tour_guides is the table name and employee_number, employee_name, hourly_rate are the three fields in the table and you need to define employee_number as primary key.
• After writing the query press enter key.

INSERT INTO TABLE

Now you need to populate the fields with the record                              

Query:

To insert a record into the table edit this query 
insert into tour_guides(employee_number,employee_name,hourly_rate)  VALUES (982,'firstname',35);
• After writing the query, execute the query
• You will get a message “1 row(s) affected”
• Insert 6 records
• At this stage you need to check whether the records are inserted into the table

Query: 

To get the records from the table,
Mysql->SELECT * FROM tour_guides;
You can able to see the table with records at the bottom.

DELETE FROM TABLE

Query:                                                                                             

You need to delete a record from the table, edit this query
Mysql->DELETE FROM tour_guides Where employee_number = 981;
After executing the query, check the table, you won’t find the record with employee_number.

ALTER TABLE TO ADD NEW COLUMN

Query: 

At this stage you need to add a field months_employed. Edit this query
Mysql->ALTER TABLE tour_guides ADD months_employed INT;
After executing the query, check the table, where you will find a field called months_employed.

UPDATE THE DATA IN TABLE

Query:                                                                                             

You need to enter record for the newly added fields.
Mysql->UPDATE tour_guides SET months_employed = 0 WHERE employee_number = 940;
Similarly populate the field with new value with the above query.
After executing the query, Check the table.

Query: 

You need to retrieve the data from the table.
Mysql-> SELECT employee_name FROM tour_guides WHERE hourly_rate >lt;30;
You can able to see the employee_name Juliet.
Great going, you finished the first exercise. Practice as many queries.

Exercise2:

Objective: 
• In this exercise you will retrieve data from multiple tables.
• Creating multiple tables.
• Retrieving data from multiple tables.
• Create two tables tour_locations and tour_expeditions
• For tour_locations have two fields location_code And location_name
• For tour_expeditions have the field location_code, employee_number, hours_worked, tourgroup_size
• Follow the naming conventions.
Solution: 
• I assume by now you can do the CREATE and INSERT Statements to populate the above tables.
The tour_guides table 

Employee_number Employee_name Hourly_rate Month_employed
978                        Christina               30                0
942                        Mary                      35                0
923                        Ranjith                  32                0
982                        Vinoth                   25                 0
Insert the same record into the table.
The tour_locations table, tour_expeditions table.
tour_locations
Insert the same records in the table.
Location_code       Location_name
1024                      Mountain
1025                      Island
1026                      National park
1027                      St Lucia
Insert the same records in the table
Location_code         Employee_number        Hours_worked        Tourgroup_size

1024                              978                                   5                         8
1027                              942                                  8                          4
1025                              923                                 3                           20
1026                              982                                 6                          8
1024                              978                                 5                         8
1025                              978                                   3                        16

Insert the same records in the table.                                                        

• Now you should be able to see the reason for the term relational database. The way these tables relate is by the common fields they have – tour expeditions joins to tour_guides through the field employee_number, and to tour_locations through location_code.

Query: 

Which employee_numbers worked in which locations? You need to use the following query:
SELECT employee_number,location_name FROM tour_location,tour_expeditions WHERE tour_location.location_code = tour_expeditions.location_code;
Which will return
Employee_number   Location_name
978                           Mountain
942                             St Lucia
923                            Island
982                           National park
978                           Mountain
978                              island
• The first part, immediately after the SELECT lists the fields we want to return. Easy enough - employee_numbers and location_name.
• The second part, after the FROM, provides the tables that contain the fields. In this case it's clearly tour_locations for location_name. But which table to choose for employee_number? Both tour_expeditions
a tour_guides contain this field. Here, we have to look at which table is related to tour_location. Since tour_location is related only to tour_expedition (through the location_code field), we could only use tour_expedition.
• And the third part, after the WHERE clause, tells us which fields the relation exists on, or are being joined.

Query: 

To bring back only the employee_numbers that gave a tour to Table Mountain, and to bring back only unique records (notice that the above query brought back a duplicate value, as there are 2 records that apply), we us
SELECT DISTINCT employee_number,location_name FROM tour_location,tour_expeditions WHERE tour_location.location_code= tour_expedition.location_code AND location_name = 'mountain' ;
Employee_number     Location_name    
978                             mountain
• Note how the DISTINCT keyword returns only one identical row. Without it, we would have returned 2 identical rows; one for each time employee 978 gave a tour to Table Mountain.

Query:                                                                                   

Now we need to return the employee_number, empoloyee_name, location_name.
The query is
SELECT DISTINCT employee_number,location_name,employee_name FROM tour_location,tour_expeditions WHERE tour_location.location_code=tour_expedition.location_code
AND location_name = 'mountain';
• Note the changes we made to our original join.
We've added employee_name to the fields returned, tour_guides to the table list, and we had to add the name of the table to the employee_number field of tour_expeditions, making it tour_expeditions.employee_number (now that there are 2 tables returning the employee_number, we need to specify which table to use.)
• In this case it makes no difference, but in others it may. And finally, we've added a join condition to the WHERE clause, pointing out the relation to use to join the 2 tables.
Employee_number             Employee_name           Location_name
978                                     Christina                       Mountain
923                                     Ranjith                          Island
978                                      Christina                      Island
982                                     Vinoth                          National park
942                                     Marry                           St Lucia
Query: 
Let’s add another record into the tour_guides into the tour_table.
INSERT into tour_guide(employee_number,employee_name) VALUES (983,'nelson');
Now run this above query again
SELECT DISTINCT tour_expeditions.employee_number,employee_name,location_name FROM tour_location, tour_expeditions,tour_guide WHERE tour_location.location_code = tour_expeditions.location_code AND tour_expeditions.employee_number=tour_guide.employee_number;
Employee_number             Employee_name        Location_name
978                                      Christina                    Mountain
923                                        Ranjith                     Island
978                                       Christina                   Island
982                                       Vinoth                      National park
942                                       Marry                       St Lucia
• This makes sense, as our new tour guide has not yet undertaken any tours. He does not yet appear in the tour_expeditions table, and so the join does not work, as there is nothing in tour_expeditions to join to.
• But what if we want all the employees back, regardless of whether they have undertaken a tour or not? We need to explicitly state this, and we do so using a LEFT JOIN (also called a LEFT OUTER JOIN).
To introduce the concept, try the following

Query: 

SELECT DISTINCT employee_name FROM tour_guide LEFT JOIN tour_expeditions ON tour_guide.employee_number = tour_expeditions.employee_number;
• Note the syntax is almost the same, except that the table names are separated by LEFT JOIN, not a comma, and ON is used for the fields to be joined, rather than WHERE.
So, going back to our original question - how do we return the employee numbers, names and locations of all guides, including those who have not yet given a tour? The query is as follows:
SELECT DISTINCT  tour_guide.employee_number, employee_name, location_name FROM tour_guide
LEFT JOIN tour_expeditions ON tour_guide.employee_number= tour_expeditions.employee_number
LEFT JOIN tour_location ON tour_location.location_code = tour_expeditions.location_code;
Employee_number     Employee_name         Location_name      
Christian                    978                                 Mountain
923                           Ranjith                              Island
978                           Christina                           Island
982                            Vinoth                              National park
942                            Marry                             St Lucia
983                             Nelson                           null

• Note that Nelson now appears in the results table, and as he has not yet leaded any tours, that field is NULL.

Query:                                                                                             

Many existing queries make use of what are called sub selects (selects within selects). For example, try the following query, which returns all employees who've worked more than a 10 hour shift:
SELECT employee_name FROM tour_guides WHERE employee_number IN (SELECT employee_name FROM tour_expeditions WHERE tourgroup_size>10)
• This query resolves in 2 steps - first the inner query (which returns 923 and 978) is resolved. Then we are left with
SELECT employee_name FROM tour_guide WHERE employee_number IN (923,978)
employee_name
Christina
Vinoth
• This resolves to the results above. But I've just demonstrated another way to do this, and which is usually a better way - the join. You can rewrite this query as:
SELECT employee_name FROM tour_guide,tour_expeditions WHERE tourgroup_size>10 AND
tour_guide.employee_number= tour_expeditions.employee_number;
Why do I say this is better? 2 reasons. One is that many DBMS's (such as early versions of MySQL) do not support nested selects.
• And the second reason is that more often they can be rewritten as a join, and the join is usually more efficient. On those big, heavily used tables, where performance is vital, you will want to do without nested
selects as much as possible.

Query: 

How could we find all tour_guides who have not yet given a tour? The query is
SELECT employee_name FROM tour_guides WHERE employee_number NOT IN (SELECT employee_number FROM tour_expeditions)
employee name

nelson                                                                                                        

But, using the same principle as before, we could rewrite this as a join, in this case a LEFT JOIN (remembering that LEFT JOINS return values that are not present).
Try the following: 
SELECT employee_name FROM tour_guides LEFT JOIN tour_expeditions ON tour_guides.employee_number = tour_expeditions.employee_number WHERE tour_expeditions.employee_number IS NULL;
• Now we see an advantage of declaring employee_number NOT NULL. It allows us to use this kind of query, which is often more efficient than the nested select, and it also saves space (the DBMS does not
have to waste space telling if the field is NULL or not--by definition it's not)

Query: 

For this exercise you need insert one more records.
INSERT INTO tour_guides VALUES ('999','Jon', 30, 5)
• Now consider another request. We want to find the names of all the employees who have the same hourly rate aschiristina. Again, we can do this with a nested select:
SELECT employee_name FROM tour_guides WHERE hourly_rate IN (select hourly_rate From tour_guides WHERE employee_name = 'Christina')
• But again, a join is preferable. In this case it will be a self-join, as all the data that we need is in the one table--tour_guides. So, we could use the following, mo re efficient, query:
SELECT e1.employee_name FROM tour_guides e1, tour_guides e2 WHERE e1.hourly_rate=e2.hourly_rate AND e2.employee_name='Christina';
Employee_name
Christina

Jon                                                                                                  

There are a few important points to notice here. We could not have used the query, as some of you may have thought,
SELECT employee_name FROM tour_guides WHERE employee_number=employee_number AND
employee_name ='Christina';
• The reason is that we need to see the table as two separate tables to be joined.
• This query only returns "Christina", satisfying the final condition.
• In order to make the DBMS see the query as a join, we need to provide an alias for the tables. We give them the names e1 and e2.
Also important is why we use e1 in SELECT e1.employee_name and e2 in e2.employee_name='Christina'. These 2 have to come from the 2 'different' versions of the table. If we chose the employee_name from the same table that we impose the condition: WHERE employee_name='Christina', of course we’re only going to get that one results back.

Exercise 3:

1.     Create a table with Name, Age, Insert 13 records with Name as

a.     Name1 to Name13, 

b.     Ages 21,25,26,35,37,38,45,46,48,50,55,52,65, 

c.      Children as 1,2,2,3,2,1,2,3,4,1,3,2,1 so on

2.     Write a Single query to count number of people with age between >lt;30, 30-40, 40-50, > 50

3.     Write a Single query to calculate no of children whose parent’s age between >lt;30, 30-40, 40-50, > 50

Good luck with your joins--remember to keep it as simple as possible for your DBMS, as few as possible nested selects, and you'll see the benefits in your applications .