activity 3-1
DESCRIPTION
ACTIVITY 3-1. START LOG FILE NAMED ACT3-1 CREATE A DATABASE NAME ACT3_1 3. CREATE A TABLE NAMED EMPLOYEE_DATA +-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/1.jpg)
ACTIVITY 3-1
![Page 2: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/2.jpg)
1. START LOG FILE NAMED ACT3-12. CREATE A DATABASE NAME ACT3_13. CREATE A TABLE NAMED EMPLOYEE_DATA +-----------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-----------+--------------+------+-----+---------+----------------+| emp_id | int(9) | NO | PRI | NULL | auto_increment | | firstname | varchar(20) | YES | | NULL | | | lastname | varchar(20) | YES | | NULL | | | title | varchar(30) | YES | | NULL | | | age | int(2) | YES | | NULL | | | y_o_s | int(2) | YES | | NULL | | | salary | decimal(8,2) | YES | | NULL | | | perks | decimal(8,2) | YES | | NULL | | | email | varchar(60) | YES | | NULL | | +-----------+--------------+------+-----+---------+----------------+
![Page 3: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/3.jpg)
1. INSERT A ROW OF DATA (RECORD) INTO TABLE
JOHN SMITH, CEO42 YRS OLD25 YEARS OF SERVICESALARY = $300000PERKS = 25000EMAIL = [email protected]
![Page 4: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/4.jpg)
insert into employee_data (firstname, lastname, title, age, y_o_s, salary, perks, email) values ("John", "Smith", "CEO", 46, 25, 300000, 25000, "[email protected]");
![Page 5: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/5.jpg)
1. DOWNLOAD EMPLOYEE.TXT FILE TO BIN DIRECTORY
2. AT COMMAND PROMPT TYPE:mysql employees <employee.txt
OR3. OPEN FILE, COPY TEXT, PASTE INTO MYSQL
WINDOW
![Page 6: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/6.jpg)
1. SHOW EVERYTHING FROM EMPLOYEE_DATA
•+--------+-----------+------------+----------------------------+------+-------+--------+-------+-----------------------+•| emp_id | firstname | lastname | title | age | y_o_s | salary | perks | email |•+--------+-----------+------------+----------------------------+------+-------+--------+-------+-----------------------+•| 1 | John | Smith | CEO | 46 | 25 | 300000 | 25000 | [email protected] | •| 2 | John | Hagan | Senior Programmer | 32 | 4 | 120000 | 25000 | [email protected] | •| 3 | Ganesh | Pillai | Senior Programmer | 32 | 4 | 110000 | 20000 | [email protected] | •| 4 | Anamika | Pandit | Web Designer | 27 | 3 | 90000 | 15000 | [email protected] | •| 5 | Mary | Anchor | Web Designer | 26 | 2 | 85000 | 15000 | [email protected] | •| 6 | Fred | Kruger | Programmer | 31 | 3 | 75000 | 15000 | [email protected] | •| 7 | John | MacFarland | Programmer | 34 | 4 | 80000 | 16000 | [email protected] | •| 8 | Edward | Sakamuro | Programmer | 25 | 2 | 75000 | 14000 | [email protected] | •| 9 | Alok | Nanda | Programmer | 32 | 3 | 70000 | 10000 | [email protected] | •| 10 | Hassan | Rajabi | Multimedia Programmer | 33 | 3 | 90000 | 15000 | [email protected] | •| 11 | Paul | Simon | Multimedia Programmer | 43 | 2 | 85000 | 12000 | [email protected] | •| 12 | Arthur | Hoopla | Multimedia Programmer | 32 | 1 | 75000 | 15000 | [email protected] | •| 13 | Kim | Hunter | Senior Web Designer | 32 | 2 | 110000 | 20000 | [email protected] | •| 14 | Roger | Lewis | System Administrator | 35 | 2 | 100000 | 13000 | [email protected] | •| 15 | Danny | Gibson | System Administrator | 34 | 1 | 90000 | 12000 | [email protected] | •| 16 | Mike | Harper | Senior Marketing Executive | 36 | 2 | 120000 | 28000 | [email protected] | •| 17 | Monica | Sehgal | Marketing Executive | 30 | 3 | 90000 | 25000 | [email protected] | •| 18 | Hal | Simlai | Marketing Executive | 27 | 2 | 70000 | 18000 | [email protected] | •| 19 | Joseph | Irvine | Marketing Executive | 27 | 2 | 72000 | 18000 | [email protected] | •| 20 | Shahida | Ali | Customer Service Manager | 32 | 3 | 70000 | 9000 | [email protected] | •| 21 | Peter | Champion | Finance Manager | 36 | 4 | 120000 | 25000 | [email protected] | •+--------+-----------+------------+----------------------------+------+-------+--------+-------+-----------------------+
![Page 7: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/7.jpg)
1. SHOW DATA FROM FIRSTNAME AND LASTNAME FIELDS ONLY
![Page 8: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/8.jpg)
mysql> select firstname, lastname from employee_data;+-----------+------------+| firstname | lastname |+-----------+------------+| John | Smith | | John | Hagan | | Ganesh | Pillai | | Anamika | Pandit | | Mary | Anchor | | Fred | Kruger | | John | MacFarland | | Edward | Sakamuro | | Alok | Nanda | | Hassan | Rajabi | | Paul | Simon | | Arthur | Hoopla | | Kim | Hunter | | Roger | Lewis | | Danny | Gibson | | Mike | Harper | | Monica | Sehgal | | Hal | Simlai | | Joseph | Irvine | | Shahida | Ali | | Peter | Champion | +-----------+------------+21 Rows in set (0.00 sec)
![Page 9: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/9.jpg)
1. INSERT A ROW OF DATA (RECORD) INTO TABLE
• HARVEY RUDOLER, FINANCIAL ANALYST• AGE – 52• YOS – 16• SALARY - $52000• PERKS - $21000• EMAIL – [email protected]
![Page 10: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/10.jpg)
mysql> insert into employee_data (firstname, lastname, title, age, y_o_s, salary, perks, email) values ("Harvey", "Rudoler", "Financial Analyst", 52, 16, 52000, 21000, "[email protected]");
![Page 11: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/11.jpg)
1. SELECT ONLY PEOPLE WITH THE FIRSTNAME “JOHN” AND SHOW FIRSTNAME AND LASTNAME FIELDS
![Page 12: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/12.jpg)
mysql> select firstname, lastname from employee_data where firstname = 'John';+-----------+------------+| firstname | lastname |+-----------+------------+| John | Smith | | John | Hagan | | John | MacFarland | +-----------+------------+3 rows in set (0.00 sec)
![Page 13: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/13.jpg)
1. SELECT ONLY PEOPLE WHO ARE PROGRAMMERS, AND SHOW THEIR FIRSTNAME AND LASTNNAME
![Page 14: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/14.jpg)
mysql> select firstname, lastname from employee_data where title = "Programmer";+-----------+------------+| firstname | lastname |+-----------+------------+| Fred | Kruger | | John | MacFarland | | Edward | Sakamuro | | Alok | Nanda | +-----------+------------+4 rows in set (0.00 sec)
![Page 15: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/15.jpg)
1. SHOW THE FIRSTNAME AND LASTNAME OF EMPLOYEES WHO ARE 32 YEARS OLD
![Page 16: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/16.jpg)
mysql> select firstname, lastname from employee_data where age = 32;+-----------+----------+| firstname | lastname |+-----------+----------+| John | Hagan | | Ganesh | Pillai | | Alok | Nanda | | Arthur | Hoopla | | Kim | Hunter | | Shahida | Ali | +-----------+----------+6 rows in set (0.00 sec)
![Page 17: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/17.jpg)
1. SHOW ALL EMPLOYEES WITH A SALARY GREATER THAN 120000
![Page 18: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/18.jpg)
mysql> select firstname, lastname from employee_data where salary > 120000;+-----------+----------+| firstname | lastname |+-----------+----------+| John | Smith | +-----------+----------+1 row in set (0.00 sec)
![Page 19: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/19.jpg)
1. SHOW ALL EMPLOYEES WITH LESS THAN 3 YEARS OF SERVICE
![Page 20: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/20.jpg)
mysql> select firstname, lastname from employee_data where y_o_s <3;+-----------+----------+| firstname | lastname |+-----------+----------+| Mary | Anchor | | Edward | Sakamuro | | Paul | Simon | | Arthur | Hoopla | | Kim | Hunter | | Roger | Lewis | | Danny | Gibson | | Mike | Harper | | Hal | Simlai | | Joseph | Irvine | +-----------+----------+10 rows in set (0.00 sec)
![Page 21: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/21.jpg)
1. SHOW ALL EMPLOYEES AND THEIR AGE AND SALARIES, WHO ARE 33 YEARS OLD OR OLDER
![Page 22: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/22.jpg)
mysql> select firstname, lastname, age, salary from employee_data where age >= 33;+-----------+------------+------+--------+| firstname | lastname | age | salary |+-----------+------------+------+--------+| John | Smith | 46 | 300000 | | John | MacFarland | 34 | 80000 | | Hassan | Rajabi | 33 | 90000 | | Paul | Simon | 43 | 85000 | | Roger | Lewis | 35 | 100000 | | Danny | Gibson | 34 | 90000 | | Mike | Harper | 36 | 120000 | | Peter | Champion | 36 | 120000 | | Harvey | Rudoler | 52 | 95000 | +-----------+------------+------+--------+9 rows in set (0.00 sec)
![Page 23: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/23.jpg)
1. SHOW ALL EMPLOYEES, THEIR AGE AND THEIR SALARIES, WHO HAVE 2 YEARS OF SERVICE OR LESS
![Page 24: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/24.jpg)
mysql> select firstname, lastname, age, salary from employee_data where y_o_s <=2;+-----------+----------+------+--------+| firstname | lastname | age | salary |+-----------+----------+------+--------+| Mary | Anchor | 26 | 85000 | | Edward | Sakamuro | 25 | 75000 | | Paul | Simon | 43 | 85000 | | Arthur | Hoopla | 32 | 75000 | | Kim | Hunter | 32 | 110000 | | Roger | Lewis | 35 | 100000 | | Danny | Gibson | 34 | 90000 | | Mike | Harper | 36 | 120000 | | Hal | Simlai | 27 | 70000 | | Joseph | Irvine | 27 | 72000 | +-----------+----------+------+--------+10 rows in set (0.00 sec)
![Page 25: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/25.jpg)
1. SHOW ALL EMPLOYEES WHOSE FIRST NAME STARTS WITH “M”
HINT: %
![Page 26: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/26.jpg)
mysql> select firstname, lastname from employee_data where firstname like "M%";+-----------+----------+| firstname | lastname |+-----------+----------+| Mary | Anchor | | Mike | Harper | | Monica | Sehgal | +-----------+----------+3 rows in set (0.00 sec)
![Page 27: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/27.jpg)
1. SHOW ALL EMPLOYEES AND THEIR TITLE, WHOSE TITLE STARTS WITH “SENIOR”
![Page 28: ACTIVITY 3-1](https://reader036.vdocuments.us/reader036/viewer/2022062323/568166e9550346895ddb2e25/html5/thumbnails/28.jpg)
mysql> select firstname, lastname, title from employee_data where title like "senior%";+-----------+----------+----------------------------+| firstname | lastname | title |+-----------+----------+----------------------------+| John | Hagan | Senior Programmer | | Ganesh | Pillai | Senior Programmer | | Kim | Hunter | Senior Web Designer | | Mike | Harper | Senior Marketing Executive | +-----------+----------+----------------------------+4 rows in set (0.00 sec)