Upon completion of this lab you should be able to: 1. Run basic scripts to install a MySQL database 2. Learn how to record output from a database. 3. Run a few basic commands. Module 3 Lab Assignment: Finding your way around in SQL The purpose of this assignment is twofold: • To introduce you to using MySQL on faure; • To help you become familiar with some common SQL commands; (You might also wish to download MySQL for your own machine, and run this program there as well.) Overview of Task For this project you will learn how to access our department MySQL server(faure) and work with a sample database provided below. The database implements a simple employees file and consists of several simple tables for organizing departments, managers, and employees. A copy of the schema for this database can be found here Links to an external site. Details Part 1: Bringing up mysql on faure 1. Log into one of the department machines, either by working in one of the CS labs, or by ssh from home. 2. run mysql by executing the following command: mysql -u loginname -D loginname -h faure -p (loginname is your NetID) 3. This should drop you into mysql and request a password. 4. Once you have entered the password (initially your CSU id), you can begin to nose around. 5. Remember that if you are using faure, you do not have permissions to create a database. Instead, one by the same name as your user id has been made available for you to use. Part 2: Create and/or use a provided database. You need to find a way to solve Questions 4-9. 1. Common mysql commands. Before you start your operation on mysql database, it's good to know the following mysql commands. Refer to the tutorial in the documentation for more detailed information. Don't forget to terminate every command with a semicolon, otherwise you will get the mysql command continuation prompt: "->". If this happens and you're done entering your command, simply enter a ";" and the command will execute. О show databases; This command shows all of the databases that your mysql server is managing. If you are running on faure, this will show your personal database available there. Database in this sense refers to a collection of tables (relations) for supporting a complete conceptual model of the world. use ; This command changes the current database to . For security reasons, on faure we do not allow databases to be created, so you must use the database of your own name here. • show tables; This command shows all of the tables (relations) for the current database (refer to the use command for changing the current database). describe ; This command describes the field names, types, and values for the table named . • select * from ; This command shows all of the data within the table named . 2. OK, now lets get to the assignment. 3. Use the tee command to put your output in a text file to submit 。 Use? at the MariaDB prompt for help 4. type in use [your database's name]; [your database's name] should be your existing database whose name is your cs department login name (NetID). Caps aren't important but spelling is. Type in show tables;" and you should see nothing. 5. Create the employees tables in the new database. First think about what kind of information you would need to create a employee database. Would you put all of it in a single table or separate it into multiple tables? A script file 6. Links to an external site. 7. is provided that you can run with mysql in batch mode to create the necessary database Run it by using the source command from within mysql. HINT: source file_name.sql in mysql prompt. 8. Examine the tables. Use the "describe" command to look at the structure of each table. Use the documentation to help understand data types, syntax, etc. Yes, you are being thrown into the deep end and having to swim, so start paddling. 9. Load the database with some data, specifically the data provided in this script file 10. Links to an external site. 11.. Execute it the same way as you did in creating the tables. The data files necessary must be local to your account and can be found in this tar file can be found here. 12. Links to an external site. 13. 14. Run some queries against the database. Here is a script file 15. Links to an external site. 16. that contains some sample queries. Examine the output to see if it makes sense. What is the difference between the first and second query? Why? What is the difference between the second and third query? Why? 9. Type in notee to stop the tee command./n ssh into the department machines. open the terminal and connect to maria db using the command I have provided in the assignment's instruction. The lab assignment introduces you to working with a MySQL database system and focuses on understanding how to navigate and query data. Here's a breakdown of the key points: Learning Objectives: • • • Install a MySQL database (not required for this lab). Record database output. Use basic SQL commands. Assignment Purpose: • • Access the department's MySQL server (faure). Practice common SQL operations on a sample employees database. Tasks: 1. Connecting to MySQL: o Login to a department machine or SSH from home. o Run mysql -u loginname -D loginname -h faure -p (replace loginname with your NetID). o Enter your password (initially your CSU ID) to connect. 2. Using the Provided Database: o Since creating databases isn't allowed on faure, you'll use a database named after your NetID. 3. Common MySQL Commands: o show databases;: Lists all databases on the server. o use ;: Selects the specified database. o show tables;: Lists all tables within the current database. o describe ;: Shows details about a specific table (structure, data types). o select * from ; Retrieves all data from a table. 4. Creating the Employee Database: o Consider what information is needed for an employee database (separate tables might be helpful). o Use the provided script file (source file_name.sql) to create the tables in your database. 5. Examining the Tables: o Use describe to view the structure of each table. o Refer to documentation for data types and syntax. 6. Loading Sample Data: o Execute the provided data script file (similar to creating tables). o The data files are located in the provided archive. 7. Running Sample Queries: o Analyze the provided script containing sample queries and their output. Explanation: Specific Questions: The prompt asks you to analyze the differences between the first three queries in the provided script but doesn't include the queries themselves. Here's a general approach to understanding the difference between SQL queries: • Identify the SELECT clause: This specifies which data you want to retrieve. • Check for FROM clause: This indicates the tables involved in the query. • Look for WHERE clause (optional): This filters data based on specific conditions. Consider ORDER BY clause (optional): This sorts the results based on a column. By comparing these elements across the queries, you can identify how they differ in terms of data selection, filtering, or sorting. Net id: C837367532 CSU id: 837367532 password: Goldentemple22!!/n Additional Notes: In the department machines. open the terminal and connect to maria db using the command I have provided in the assignment's instruction. After login into one of the department machines, open the terminal and write this command mysql -u loginname -D loginname -h faure -p using your NETID in the place of loginname Go to SNA's website and follow the instructions in how to remotely access CS machines. Once you have the access open the terminal and follow the command I provided in LAB3 to connect to MariaDB. You need to use globalProtect to get access. Look at the website about vpn.- C8373675329:51 pm Goldentemple22!!

Solved: Upon completion of this lab you should be able to: 1. Run basic scrip

Home
questions and answers
upon completion of this lab you should be able to 1 run basic scripts

Question

Upon completion of this lab you should be able to: 1. Run basic scripts to install a MySQL database 2. Learn how to record output from a database. 3. Run a few basic commands. Module 3 Lab Assignment: Finding your way around in SQL The purpose of this assignment is twofold: • To introduce you to using MySQL on faure;