in unix/linus cloud 9 in mysql have two table have to do view option on the table not sure how tro do it someone pls help it is number 29

Check to see if the MySQL database is installed and what version you have: mysql --version

If it is not installed, then install it: mysql-ctl install Aftering installing it, youll receive the following message: MySQL 5.5 database added. Please make note of these credentials DO make note of the credentials.

After installing MySQL, you can use it: mysql -u {username} Note: You MUST terminate each line with a semicolon (;)

View your current databases: show databases; Do not touch these existing databases. Note that there is a pre-installed database for phpmyadmin, a very popular MySQL GUI.

Now, lets create a database: create database test_db;

You have a database! Lets add a table. First, you must step into your database. use test_db;

Now, lets put some data into that table.

Take a peek at our tables: show tables;

View the data: select * from Users;

Insert more data: insert into Users (Id, LastName, FirstName) values (23, 'blair', 'fred'), (234, 'dell', 'rick'), (11, 'jones', 'jim'), (233, 'clark', 'alex'), (111, 'hmm', 'oddfellow'), (22, 'smith', 'fred'), (323423, 'chatow', 'russ');

Now select the users last name whose Id is equal to 23: select LastName from Users where Id = 23;

Lets inset yet another value. Heres the values (23, hackett, frederick). You do the work.

Now, view your table. Awe shucks! We have two ids that are the same. Thats not cool. That was how we were going to differentiate are users.

Go ahead and delete your previous entry. Heres the syntax: (DELETE FROM table_name WHERE condition;). You do the deletion. Heres what your result should look like. Awe shucks. Both hackett and blair where gone. Hmm. We need to do something about that. We need a primary key and we want our Id to be the primary key. Heres the rules for primary keys.

The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only one primary key, which may consist of single or multiple fields.

We need to alter our table. Whats alter table?

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Heres what you need to do: alter table Users add primary key (Id);

Now, lets take a look at the schema. show columns from Users; It should look like this:

Go ahead and add Fred Blair back to your table. (23, 'blair', 'fred')

Now, add Frederick Hackett (23, hackett, frederick) Didnt work, right? Good. Thats the point of primary keys.

Go ahead and update the Id to 24 and add Frederick to the table. Your table should like this when youre finished.

Now lets create a table. Heres the syntax. create table sports (Id int not null, UserId int not null, FavSport varchar(255), primary key (Id), foreign key (UserId) references Users(Id)); Ahh...You have a foreign key. Whats that?

A FOREIGN KEY is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

Before moving on, verify the table schema. You know how to do that, right?

Now, lets insert some data. Im not going to insert for you. Thats your job. Heres the data. Now, make it happen. (1, 1, 'Ping Pong'), (2, 23, 'Tennis'), (3, 23, 'Basketball'), (4, 23, 'Football'), (5, 234, 'Soccer')

Ping Pongs not a sport. That should be wrestling. You can change with the update syntax. Here it is: UPDATE table_name SET column1 = value1, column2 = value2, WHERE condition; Now, change it.

Now, view the data in the sports table. It should look like this:

Now, lets discuss joins - A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

There are several different types of joins, but we are going to use a right join - The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match. You can read more about left joins here: https://www.w3schools.com/sql/sql_join_right.asp

Wed like to see everyones favorite. Heres the left join syntax for that. select * from Users left join sports on Users.Id = sports.UserId; As you can see, thats useful.

Now, lets discuss views.

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

You can read more here: https://www.w3schools.com/sql/sql_view.asp

29.In short, you can create a view as follows: create view ViewName as {query} Create a view for all users who have an Id less than 100. Heres the syntax for that.

Now view your view. select * from LessThan100; Heres what you should have.