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!!
