Setting up structure of tables :
mysql> create table students (studid int(9) not null primary key, lastname char(25), firstname char(25), address varchar(80), phone char(12), city varchar(30), email varchar(105));mysql> create table courses (code varchar(6) not null primary key, coursedesc varchar(100), room varchar(8), hours int(2), costperhour int(3));
mysql> create table grades (recordnum int(4) not null primary key auto_increment, studid int(9), code varchar(6), semester varchar(4), grade int(2));
Adding Data from a file to the tables :
mysql> load data local infile 'studentsdata.txt' into table students lines terminated by '\r\n';
mysql> load data local infile 'coursesdata.txt' into table courses lines terminated by '\r\n';
mysql> load data local infile 'gradesdata.txt' into table grades lines terminated by '\r\n';
Insert foreign key into the tables:
mysql> alter table grades add constraint foreign key(studid) references students(studid);
mysql> alter table grades add constraint foreign key(code) references courses(code);
Structutre of tables :
mysql> desc students;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| studid | int(9) | NO | PRI | NULL | |
| lastname | char(25) | YES | | NULL | |
| firstname | char(25) | YES | | NULL | |
| address | varchar(80) | YES | | NULL | |
| phone | char(12) | YES | | NULL | |
| city | varchar(30) | YES | | NULL | |
| email | varchar(105) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
mysql> desc courses;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| code | varchar(6) | NO | PRI | NULL | |
| coursedesc | varchar(100) | YES | | NULL | |
| room | varchar(8) | YES | | NULL | |
| hours | int(2) | YES | | NULL | |
| costperhour | int(3) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
mysql> desc grades;
+-----------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+----------------+
| recordnum | int(4) | NO | PRI | NULL | auto_increment |
| studid | int(9) | YES | MUL | NULL | |
| code | varchar(6) | YES | MUL | NULL | |
| semester | varchar(4) | YES | | NULL | |
| grade | int(2) | YES | | NULL | |
+-----------+------------+------+-----+---------+----------------+
Database in tables:
mysql> select * from students;
+--------+-----------+-----------+-------------------+--------------+-----------
-------+-------------------------------+
| studid | lastname | firstname | address | phone | city
| email |
+--------+-----------+-----------+-------------------+--------------+-----------
-------+-------------------------------+
| 12345 | Apted | Sam | 42 Wild Way | 416-456-3456 | Toronto
| Apted@learn.senecac.on.ca |
| 12346 | English | Jane | 218 Emerick | 312-234-4456 | North York
| English@learn.senecac.on.ca |
| 12435 | Rudoler | Harvey | 45 Third | 416-333-3456 | Toronto
| Rudoler@learn.senecac.on.ca |
| 21435 | Smith | John | 34 Second | 905-394-3336 | Toronto
| Smith@learn.senecac.on.ca |
| 22222 | Jones | Mary | 456 Coxwell | 905-367-7858 | Scarberia
| Walsh@learn.senecac.on.ca |
| 22223 | Walton | Mary | N/A | N/A | N/A
| Ryan@learn.senecac.on.ca |
| 22345 | Davis | Susan | 34 Walter Way | 566-346-6738 | Ridgeway
| Walton@learn.senecac.on.ca |
| 23965 | Walton | Walter | 56 Second Av | 444-567-2345 | Keswick
| Walton@learn.senecac.on.ca |
| 29684 | Obama | Barak | White House | Unlisted | Washington
| bobama@pres.dc.us |
| 30495 | Jameson | Todd | 999 Queen St | 999-999-9990 | Scarberia
| Jameson@learn.senecac.on.ca |
| 34956 | Rubble | Barney | 34 First Av | 416-444-5678 | Hammilton
| Rubble@learn.senecac.on.ca |
| 43215 | Snood | Marvin | N/A | N/A | N/A
| Snood@learn.senecac.on.ca |
| 43977 | Sneed | Harvey | N/A | N/A | N/A
| Sneed@learn.senecac.on.ca |
| 43984 | Claus | Santa | 43 North Pole Dr | N/A | N/A
| Claus@learn.senecac.on.ca |
| 45679 | Dawes | Fred | 34 Ninth St | 456-456-2345 | Sleppy Hol
low | Dawes@learn.senecac.on.ca |
| 48632 | Knowles | Beyonce | 1 Hit Street | Unlisted | Hollywood
| bknowles@star.ca.us |
| 55555 | Peltier | Sam | N/A | N/A | N/A
| Peltier@learn.senecac.on.ca |
| 56792 | Chan | Wai-Chu | 34 Phipps St | 905-456-3302 | Port Arthu
r | Chan@learn.senecac.on.ca |
| 65434 | Snozer | Larry | 34 Wild Way | 555-345-5638 | Orillia
| Snozer@learn.senecac.on.ca |
| 65734 | Lone | Lucy | 34 Wilderness Way | 555-346-5638 | Oro
| Lone@learn.senecac.on.ca |
| 69043 | Batman | Robin | 1 Hero Way | 911-911-9111 | Gotham Cit
y | brobin@hero.gc.com |
| 80384 | Cliff | James | 4736 Corth Dr | 453-926-9875 | Hamiltion
Hilles | Cliff@learn.senecac.on.ca |
| 81284 | Pyle | Gomer | 4736 Siner Dr | 453-926-9875 | Holy Hillb
illies | Pyle@learn.senecac.on.ca |
| 84984 | Claude | Sarah | 476 North Dr | 453-876-9875 | Hamiltion
| Claude@learn.senecac.on.ca |
| 85734 | Smith | Laura | 34 Water Way | 566-346-5638 | Orono
| Smith@learn.senecac.on.ca |
| 85974 | Cooper | Bradley | 1 Handsome Rd | 123-456-9856 | Hollywood
| bcooper@handsom.com |
| 97233 | Boxwell | Bob | N/A | N/A | N/A
| Boxwell@learn.senecac.on.ca |
| 97384 | Fleming | Rhonda | N/A | N/A | N/A
| Feming@learn.senecac.on.ca |
| 97546 | Souder | Allan | 45 Night Av | 416-398-3049 | East York
| Souder@learn.senecac.on.ca |
| 98765 | Appleseed | Scott | N/A | N/A | East York
| Appleseed@learn.senecac.on.ca |
| 98987 | Cramden | Ralph | N/A | N/A | N/A
| Cramden@learn.senecac.on.ca |
+--------+-----------+-----------+-------------------+--------------+-----------
-------+-------------------------------+
31 rows in set (0.00 sec)
mysql> select * from courses;
+--------+--------------------+-------+-------+-------------+
| code | coursedesc | room | hours | costperhour |
+--------+--------------------+-------+-------+-------------+
| APL701 | Cross Platform | T3425 | 4 | 25 |
| DAT702 | Database Admin | T4046 | 5 | 30 |
| DCN386 | DataCommunications | S3456 | 4 | 25 |
| EAC150 | College English | S2110 | 4 | 19 |
| HWD101 | Hardware | T4567 | 4 | 60 |
| INT213 | ASP Pages | S3456 | 4 | 35 |
| INT620 | Internet-Advanced | S3343 | 3 | 45 |
| NET401 | NOVELL NETWARE | T3132 | 5 | 50 |
| OPS235 | FirstInstall | S3343 | 4 | 25 |
| OPS335 | Unix II | T4048 | 5 | 100 |
| OPS400 | Intro to Internet | T4048 | 5 | 48 |
| OPS435 | Unix Again | T3423 | 4 | 95 |
| OPS440 | Unix Admin | S2212 | 5 | 90 |
| SEC520 | Security4All | S2235 | 4 | 15 |
| SEC703 | Security-Advanced | T4050 | 4 | 10 |
| ULI101 | Intro to Internet | T4046 | 5 | 36 |
+--------+--------------------+-------+-------+-------------+
16 rows in set (0.00 sec)
mysql> select * from grades;
+-----------+--------+--------+----------+-------+
| recordnum | studid | code | semester | grade |
+-----------+--------+--------+----------+-------+
| 1 | 12345 | DAT702 | 043 | 85 |
| 2 | 12345 | OPS400 | 043 | 75 |
| 3 | 12345 | INT213 | 051 | 35 |
| 4 | 12346 | OPS400 | 043 | 65 |
| 5 | 12346 | OPS440 | 051 | 65 |
| 6 | 12346 | OPS400 | 052 | 85 |
| 7 | 21435 | OPS440 | 042 | 65 |
| 8 | 21435 | OPS400 | 051 | 35 |
| 9 | 21435 | INT213 | 052 | 69 |
| 10 | 22222 | HWD101 | 062 | 95 |
| 11 | 98765 | OPS400 | 053 | 78 |
| 12 | 97546 | DAT702 | 053 | 35 |
| 13 | 97546 | DAT702 | 061 | 65 |
| 14 | 97546 | DAT702 | 061 | 65 |
| 15 | 21435 | DAT702 | 033 | 69 |
| 16 | 12345 | INT213 | 052 | 85 |
| 17 | 12346 | OPS400 | 991 | 75 |
| 18 | 55555 | HWD101 | 043 | 45 |
| 19 | 43215 | OPS400 | 993 | 8 |
| 20 | 97384 | INT213 | 031 | 59 |
| 21 | 43977 | DAT702 | 023 | 38 |
| 22 | 22223 | OPS440 | 043 | 88 |
| 23 | 97233 | NET401 | 023 | 91 |
| 24 | 98987 | HWD101 | 031 | 23 |
| 25 | 55555 | INT213 | 993 | 33 |
| 26 | 97233 | OPS440 | 023 | 55 |
| 27 | 97384 | INT213 | 043 | 78 |
| 28 | 43977 | DAT702 | 031 | 87 |
| 29 | 22223 | OPS440 | 023 | 12 |
| 30 | 97233 | NET401 | 993 | 77 |
| 31 | 98987 | HWD101 | 043 | 77 |
| 32 | 55555 | OPS400 | 023 | 34 |
| 33 | 43215 | HWD101 | 993 | 67 |
| 34 | 97384 | NET401 | 043 | 87 |
| 35 | 43977 | OPS400 | 031 | 36 |
| 36 | 22223 | OPS400 | 993 | 67 |
| 37 | 97233 | HWD101 | 043 | 43 |
| 38 | 98987 | INT213 | 993 | 76 |
| 39 | 22223 | OPS400 | 052 | 19 |
| 40 | 22223 | INT213 | 052 | 67 |
| 41 | 22223 | NET401 | 052 | 45 |
| 42 | 22222 | NET401 | 061 | 75 |
| 43 | 97384 | NET401 | 062 | 76 |
| 44 | 97384 | INT213 | 062 | 56 |
| 45 | 55555 | INT213 | 061 | 56 |
| 46 | 48632 | SEC703 | 023 | 95 |
| 47 | 29684 | INT213 | 052 | 89 |
| 48 | 69043 | INT213 | 023 | 54 |
| 49 | 85974 | NET401 | 052 | 32 |
| 50 | 29684 | OPS235 | 061 | 55 |
| 51 | 29684 | INT213 | 052 | 39 |
+-----------+--------+--------+----------+-------+
51 rows in set (0.00 sec)
General Practice on Tables :
1.) mysql> select studid, grade from grades where code ="HWD101" group by grade >= 40;
+--------+-------+
| studid | grade |
+--------+-------+
| 98987 | 23 |
| 22222 | 95 |
+--------+-------+
2 rows in set (0.00 sec)
2.) mysql> select count(studid) from grades where semester=061;
+---------------+
| count(studid) |
+---------------+
| 5 |
+---------------+
1 row in set (0.00 sec)
3.) mysql> select semester, count(code) from grades group by semester order by semester;
+----------+-------------+
| semester | count(code) |
+----------+-------------+
| 023 | 7 |
| 031 | 4 |
| 033 | 1 |
| 042 | 1 |
| 043 | 9 |
| 051 | 3 |
| 052 | 9 |
| 053 | 2 |
| 061 | 5 |
| 062 | 3 |
| 991 | 1 |
| 993 | 6 |
+----------+-------------+
12 rows in set (0.00 sec)
4.) mysql> select firstname, lastname from students where firstname like "S%";
+-----------+-----------+
| firstname | lastname |
+-----------+-----------+
| Sam | Apted |
| Susan | Davis |
| Santa | Claus |
| Sam | Peltier |
| Sarah | Claude |
| Scott | Appleseed |
+-----------+-----------+
6 rows in set (0.00 sec)
5.) mysql> select distinct code as class, count(studid) from grades group by class;
+--------+---------------+
| class | count(studid) |
+--------+---------------+
| DAT702 | 7 |
| HWD101 | 6 |
| INT213 | 13 |
| NET401 | 7 |
| OPS235 | 1 |
| OPS400 | 11 |
| OPS440 | 5 |
| SEC703 | 1 |
+--------+---------------+
8 rows in set (0.00 sec)
6.) mysql> select studid, grade from grades where semester=052 and code="INT213" group by studid having grade >= 50;
+--------+-------+
| studid | grade |
+--------+-------+
| 12345 | 85 |
| 21435 | 69 |
| 22223 | 67 |
| 29684 | 89 |
+--------+-------+
4 rows in set (0.00 sec)
7.) mysql> select sum(costperhour * hours) from courses where code="HWD101" or code="OPS400";
+--------------------------+
| sum(costperhour * hours) |
+--------------------------+
| 480 |
+--------------------------+
1 row in set (0.00 sec)
8.) mysql> select max(grade), code from grades group by code;
+------------+--------+
| max(grade) | code |
+------------+--------+
| 87 | DAT702 |
| 95 | HWD101 |
| 89 | INT213 |
| 91 | NET401 |
| 55 | OPS235 |
| 85 | OPS400 |
| 88 | OPS440 |
| 95 | SEC703 |
+------------+--------+
8 rows in set (0.00 sec)
9.) mysql> select avg(grade) as AVERAGE_OPS400 from grades where code="OPS400";
+----------------+
| AVERAGE_OPS400 |
+----------------+
| 52.4545 |
+----------------+
1 row in set (0.00 sec)
10.)
11.)