1 / 28

Employee Database Creation and Management in MySQL

This activity covers the creation of an employee database named "ACT3_1" and a table called "employee_data". The table includes fields for employee details such as ID, first name, last name, title, age, years of service, salary, perks, and email. Users will learn how to insert data into the table, including a record for John Smith, and display specific fields from the database. The tutorial also includes steps to download employee data and execute SQL queries to filter records.

kumiko
Télécharger la présentation

Employee Database Creation and Management in MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ACTIVITY 3-1

  2. 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 | +-----------+--------------+------+-----+---------+----------------+ | 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 | | +-----------+--------------+------+-----+---------+----------------+

  3. INSERT A ROW OF DATA (RECORD) INTO TABLE JOHN SMITH, CEO 42 YRS OLD 25 YEARS OF SERVICE SALARY = $300000 PERKS = 25000 EMAIL = JSMITH@MURDOCHINC.COM

  4. insert into employee_data (firstname, lastname, title, age, y_o_s, salary, perks, email) values ("John", "Smith", "CEO", 46, 25, 300000, 25000, "jsmith@murdochinc.com");

  5. DOWNLOAD EMPLOYEE.TXT FILE TO BIN DIRECTORY • AT COMMAND PROMPT TYPE: mysql employees <employee.txt OR 3. OPEN FILE, COPY TEXT, PASTE INTO MYSQL WINDOW

  6. 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 | jsmith@murdochinc.com | • | 2 | John | Hagan | Senior Programmer | 32 | 4 | 120000 | 25000 | john_hagan@bignet.com | • | 3 | Ganesh | Pillai | Senior Programmer | 32 | 4 | 110000 | 20000 | g_pillai@bignet.com | • | 4 | Anamika | Pandit | Web Designer | 27 | 3 | 90000 | 15000 | ana@bignet.com | • | 5 | Mary | Anchor | Web Designer | 26 | 2 | 85000 | 15000 | mary@bignet.com | • | 6 | Fred | Kruger | Programmer | 31 | 3 | 75000 | 15000 | fk@bignet.com | • | 7 | John | MacFarland | Programmer | 34 | 4 | 80000 | 16000 | john@bignet.com | • | 8 | Edward | Sakamuro | Programmer | 25 | 2 | 75000 | 14000 | eddie@bignet.com | • | 9 | Alok | Nanda | Programmer | 32 | 3 | 70000 | 10000 | alok@bignet.com | • | 10 | Hassan | Rajabi | Multimedia Programmer | 33 | 3 | 90000 | 15000 | hasan@bignet.com | • | 11 | Paul | Simon | Multimedia Programmer | 43 | 2 | 85000 | 12000 | ps@bignet.com | • | 12 | Arthur | Hoopla | Multimedia Programmer | 32 | 1 | 75000 | 15000 | arthur@bignet.com | • | 13 | Kim | Hunter | Senior Web Designer | 32 | 2 | 110000 | 20000 | kim@bignet.com | • | 14 | Roger | Lewis | System Administrator | 35 | 2 | 100000 | 13000 | roger@bignet.com | • | 15 | Danny | Gibson | System Administrator | 34 | 1 | 90000 | 12000 | danny@bignet.com | • | 16 | Mike | Harper | Senior Marketing Executive | 36 | 2 | 120000 | 28000 | mike@bignet.com | • | 17 | Monica | Sehgal | Marketing Executive | 30 | 3 | 90000 | 25000 | monica@bignet.com | • | 18 | Hal | Simlai | Marketing Executive | 27 | 2 | 70000 | 18000 | hal@bignet.com | • | 19 | Joseph | Irvine | Marketing Executive | 27 | 2 | 72000 | 18000 | joseph@bignet.com | • | 20 | Shahida | Ali | Customer Service Manager | 32 | 3 | 70000 | 9000 | shahida@bignet.com | • | 21 | Peter | Champion | Finance Manager | 36 | 4 | 120000 | 25000 | peter@bignet.com | • +--------+-----------+------------+----------------------------+------+-------+--------+-------+-----------------------+

  7. SHOW DATA FROM FIRSTNAME AND LASTNAME FIELDS ONLY

  8. 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)

  9. INSERT A ROW OF DATA (RECORD) INTO TABLE • HARVEY RUDOLER, FINANCIAL ANALYST • AGE – 52 • YOS – 16 • SALARY - $52000 • PERKS - $21000 • EMAIL – HRUDOLER@MURDOCHINC.COM

  10. mysql> insert into employee_data (firstname, lastname, title, age, y_o_s, salary, perks, email) values ("Harvey", "Rudoler", "Financial Analyst", 52, 16, 52000, 21000, "hrudoler@murdochinc.com");

  11. SELECT ONLY PEOPLE WITH THE FIRSTNAME “JOHN” AND SHOW FIRSTNAME AND LASTNAME FIELDS

  12. 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)

  13. SELECT ONLY PEOPLE WHO ARE PROGRAMMERS, AND SHOW THEIR FIRSTNAME AND LASTNNAME

  14. 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)

  15. SHOW THE FIRSTNAME AND LASTNAME OF EMPLOYEES WHO ARE 32 YEARS OLD

  16. 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)

  17. SHOW ALL EMPLOYEES WITH A SALARY GREATER THAN 120000

  18. mysql> select firstname, lastname from employee_data where salary > 120000; +-----------+----------+ | firstname | lastname | +-----------+----------+ | John | Smith | +-----------+----------+ 1 row in set (0.00 sec)

  19. SHOW ALL EMPLOYEES WITH LESS THAN 3 YEARS OF SERVICE

  20. 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)

  21. SHOW ALL EMPLOYEES AND THEIR AGE AND SALARIES, WHO ARE 33 YEARS OLD OR OLDER

  22. 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)

  23. SHOW ALL EMPLOYEES, THEIR AGE AND THEIR SALARIES, WHO HAVE 2 YEARS OF SERVICE OR LESS

  24. 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)

  25. SHOW ALL EMPLOYEES WHOSE FIRST NAME STARTS WITH “M” HINT: %

  26. 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)

  27. SHOW ALL EMPLOYEES AND THEIR TITLE, WHOSE TITLE STARTS WITH “SENIOR”

  28. 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)

More Related