Question

5. 2. Challenge

Defining the reference table

A mysql prompt has been opened for you.

Using the college database, complete the following tasks (use either a single-line or a multi-line SQL statement):

Create a courses table with this definition (try using a multi-line SQL statement):

+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | int(3) unsigned     | NO   | PRI | NULL    | auto_increment |
| title   | varchar(255)        | NO   | UNI | NULL    |                |
| credits | tinyint(2) unsigned | NO   |     | 1       |                |
+---------+---------------------+------+-----+---------+----------------+

Answer 1

5.2 Consider a new database named "college" has been created. For creating a table courses in the college database, it is necessary to use the college database, then create a table in the database 1) The SQL query used to create a table in the database is CREATE command. The SQL statement to create a table "course" in the college database is as follows: USE college; CREATE TABLE courses id INT(3) UNSIGNED NOT NULL AUTO _INCREMENT, title VARCHAR(255) NOT NULL, credits TINYINT(2)UNSIGNED NOT NULL DEFAULT 1, PRIMARY KEY(id), UNIQUE KEY(title) ); The screenshot of the above query is as follows:

Run SQL query/queries on server "MySQL": 1 USE college; 3 CREATE TABLE Courses id INT (3) UNSIGNED NOT NULL AUTO INCREMENT, title VARCHAR (255) NOT NULL credits TINYINT (2) UNSIGNED NOT NULL DEFAULT 1, PRIMARY KEY(id), UNIQUE KEY(title) 4 6 8 9 The output of the above SQL statements are as follows: MYSQL returned an empty result set (i.e. zero rows). (Query took 0.0005 seconds.) USE college [Edit MySQL returned an empty result set (i.e. zero rows). (Query took 0.0145 seconds) CREATE TABLE courses(id INT(3) UNSIGNED NOT NULL AUTO INCREMENT, title VARCHAR(255) NOT NULL, credits TINYINT (2) UNSIGNED NOT NULL DEFAULT 1, PRIMARY KEY(id), UNIQUE KEY(title) ) [Edit

The structure of the "courses" table in the college database is as follows: Your SQL query has been executed successfully. DESCRIBE courses Options Туре int(3) unsigned varchar(255) credits tinyint(2) unsigned NO Field Null Key Default Extra id NO PRI NULL auto_increment UNI NULL title NO 1

SQL Statements to Copy:

The SQL statement to create a table “course” in the college database is as follows:

            USE college;

            CREATE TABLE courses(

                        id INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,

                        title VARCHAR(255) NOT NULL,

                        credits TINYINT(2) UNSIGNED NOT NULL DEFAULT 1,

                        PRIMARY KEY(id),

                        UNIQUE KEY(title)

            );

NOTE:

1) Before executing the SQL query, create a college database using

CREATE DATABASE college;

2) These sql statements will run on PhpMyAdmin or MY SQL but might not work on the online sql compiler.