Question

Part #2: Creating a table

Background: Prior to storing data in a RDBMS, a table with suitable schema must be created.

Exercise: Create a table with your ID (e.g.: derricker) with the following procedure

1. If needed, log onto MySQL RDBMS using the mysql command-line.
2. Create a table to store person information (namely: id, name, and email) by appropriately changing ID in the following command:

mysql> CREATE TABLE id (    id    INTEGER NOT NULL,    name VARCHAR(64) NOT NULL,    email VARCHAR(32) NOT NULL,    PRIMARY KEY (id) );

4. If the table is successfully created, you should get the message "Query OK, 0 rows affected". If you get an error, get help from to resolve the error.

F	NOTE: You will need to complete this part of the exercises successfully prior to proceeding with remainder of this exercise. So, if you get error messages ensure you get help from your instructor to resolve the error.

Part #3: Insert some data into your table

Background: One of the key operations of working with a database is inserting new values into the database. This is accomplished using SQL insert statement.

Exercise: Insert sample data into your ID table that you created in the previous step with the following command:

1. If needed, log onto MySQL RDBMS using the mysql command-line introduced earlier.
2. Insert a row into the table using the SQL insert statement below:

INSERT INTO id (id, name, email) VALUES (100, 'John Doe', '[email protected]');

3. Check to ensure that you get "Query OK, 1 row affected" as the response indicating 1 row was successfully inserted. If you get errors get help from your instructor to resolve the issue.
4. Suitably modify the insert command to insert the following 3 rows to your table:

id	name	email
200	'Mary Doe'	[email protected]
300	'James Bond'	[email protected]
400	'Superman'	[email protected]

F	NOTE: You will need to complete this part of the exercises successfully prior to proceeding with remainder of this exercise. So if you get error messages ensure you get help from your instructor to resolve the error.

Part #4: Experiment with SQL queries

Background: Querying data using SQL involves coding suitable SELECT statements with necessary columns and conditions.

Exercise: Using the SELECT statements discussed in class, code the SQL statement for the following queries:

1. Develop a SQL query to list all rows in your table (copy-paste your SQL statement and output in the space below):

2. Develop a SQL query to list just name of persons with ID less than 400 (copy-paste your SQL statement and output in the space below):

3. Develop a SQL query to list id and name of persons that have the word 'Doe' in their name using the LIKE comparison operator (copy-paste your SQL statement and output in the space below):

4. Develop a SQL query to list id and name of persons, with data sorted by name (copy-paste your SQL statement and output in the space below):

Answer 1

1. To create a table, follow the table syntax in mysql.

Let the table name be Sam.

CREATE TABLE Sam
(SID int,Name varchar(64),Email varchar(32));

You will get a Query ok message.

I have inserted a sample extra data with the name samy.

INSERT into Sam(SID,Name,Email)Values('200','Mary Doe','[email protected]');

You will get a message 1 Row Affected once you insert data.

INSERT into Sam(SID,Name,Email)Values('300','James Bond','[email protected]');

You will get a message 1 Row Affected once you insert data.

INSERT into Sam(SID,Name,Email)Values('400','Superman','[email protected]');

You will get a message 1 Row Affected once you insert data.

To select all the rows from the statement, use the following code

SELECT * from Sam;

The result will show the table sam we created along with the data, I will upload the w3school try version output as i dont have mysql in my system.

To display only the names of person with id less than 400, run the following code.

SELECT Name from Sam where SID<'400';

The output will give the name of all except superman whose id is not less than 400. The single inverted coma is important, without it, the result will show all the table rows.

SELECT SID,Name from Sam where Name LIKE '%Doe';

The % operator is used to indicate any name with our match pattern Doe . Without this operator the result will show no match found.

Order by is the command used for sorting in mysql. The command is as follows.

SELECT Sid,Name FROM Sam ORDER BY Name;

Here the name is sorted.