MySQL Assignment -1 (DDL)
1. Login to MySQL and view all databases already present. You should get following result :
mysql> use mysql;
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| assigment1 |
| information_schema |
| mysql |
| performance_schema |
| sample |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql>
2. Write an SQL statement to create a simple table countries including columns
country_id,country_name and region_id. After this display the structure of table as
below :
mysql> create table countries
-> (country_id int(11),
-> country_name varchar(20),
-> region_id int(11));
Query OK, 0 rows affected, 2 warnings (0.79 sec)
mysql> desc countries;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| country_id | int | YES | | NULL | |
| country_name | varchar(20) | YES | | NULL | |
| region_id | int | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
3. Write an SQL statement to create a table named jobs including columns job_id, job_title,
min_salary, max_salary and check whether the max_salary amount exceeding the upper
limit 25000. Also set job_id as primary key and entering null values for job_title is not
allowed.
mysql> create table jobs
-> (job_id int,
-> job_title varchar(20),
-> min_salary decimal(5,0),
-> max_salary decimal(5,0));
Query OK, 0 rows affected (1.20 sec)
mysql> desc jobs;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| job_id | int | YES | | NULL | |
| job_title | varchar(20) | YES | | NULL | |
| min_salary | decimal(5,0) | YES | | NULL | |
| max_salary | decimal(5,0) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table jobs
-> add primary key(job_id);
Query OK, 0 rows affected (1.87 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table jobs
-> modify column job_title varchar(20) not null;
Query OK, 0 rows affected (1.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc jobs
-> ;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| job_id | int | NO | PRI | NULL | |
| job_title | varchar(20) | NO | | NULL | |
| min_salary | decimal(5,0) | YES | | NULL | |
| max_salary | decimal(5,0) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table jobs
-> modify max_salary decimal(5,0)check(max_salary>25000);
Query OK, 0 rows affected (2.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
4. Write a SQL statement to create a table named job_histry including columns
employee_id, start_date, end_date, job_id and department_id
mysql> create table job_histry
-> (employee_id int,
-> start_date date,
-> end_date date,
-> job_id int,
-> department_id int);
Query OK, 0 rows affected (0.52 sec)
mysql> desc job_histry;
+---------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------+------+-----+---------+-------+
| employee_id | int | YES | | NULL | |
| start_date | date | YES | | NULL | |
| end_date | date | YES | | NULL | |
| job_id | int | YES | | NULL | |
| department_id | int | YES | | NULL | |
+---------------+------+------+-----+---------+-------+
5 rows in set (0.00 sec)
5. Write an SQL statement to alter a table named countries to make sure that no duplicate
data against column country_id will be allowed at the time of insertion.
mysql> desc jobs;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| job_id | int | NO | PRI | NULL | |
| job_title | varchar(20) | NO | | NULL | |
| min_salary | decimal(5,0) | YES | | NULL | |
| max_salary | decimal(5,0) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table jobs
-> modify column job_title varchar(20)default ' ';
Query OK, 0 rows affected (2.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc jobs;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| job_id | int | NO | PRI | NULL | |
| job_title | varchar(20) | YES | | | |
| min_salary | decimal(5,0) | YES | | NULL | |
| max_salary | decimal(5,0) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table jobs
-> modify column min_salary decimal(5,0)default 8000;
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc jobs;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| job_id | int | NO | PRI | NULL | |
| job_title | varchar(20) | YES | | | |
| min_salary | decimal(5,0) | YES | | 8000 | |
| max_salary | decimal(5,0) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table jobs
-> modify column Max_salary decimal(5,0) default NULL;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc jobs;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| job_id | int | NO | PRI | NULL | |
| job_title | varchar(20) | YES | | | |
| min_salary | decimal(5,0) | YES | | 8000 | |
| Max_salary | decimal(5,0) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
6. Write an SQL statement to create a table named jobs including columns job_id, job_title,
min_salary and max_salary, and make sure that, the default value for job_title is blank and
min_salary is 8000 and max_salary is NULL will be entered automatically at the time of
insertion if no value assigned for the specified columns.
mysql> create table department2
-> (DEPARTMENT_ID DECIMAL(4,0),
-> DEPARTMENT_NAME VARCHAR(30),
-> MANAGER_ID DECIMAL (6,0),
-> LOCATION_ID DECIMAL(4,0))
-> ;
ERROR 1050 (42S01): Table 'department2' already exists
mysql> DESC DEPARTMENT2;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| DEPARTMENT_ID | int | YES | | NULL | |
| DEPARTMENT_NAME | varchar(30) | YES | | NULL | |
| MANAGER_ID | int | YES | | NULL | |
| LOCATIOPN_ID | int | YES | | NULL | |
+-----------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE DEPARTMENT2
-> ADD PRIMARY KEY (DEPARTMENT_ID, MANAGER_ID);
Query OK, 0 rows affected (1.70 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC DEPARTMENT2;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| DEPARTMENT_ID | int | NO | PRI | NULL | |
| DEPARTMENT_NAME | varchar(30) | YES | | NULL | |
| MANAGER_ID | int | NO | PRI | NULL | |
| LOCATIOPN_ID | int | YES | | NULL | |
+-----------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
7. Create a Department table with following structure . Write an SQL statement to create a
table employees including columns employee_id, first_name, last_name, email,
phone_number hire_date, job_id, salary, commission, manager_id and department_id and
make sure that, the employee_id column does not contain any duplicate value at the time of
insertion and the foreign key columns combined by department_id and manager_id columns
contain only those unique combination values, which combinations are exists in the
departments table
mysql> CREATE TABLE EMP0 (
-> EMPLOYEE_ID INT PRIMARY KEY,
-> FIRST_NAME VARCHAR(50),
-> LAST_NAME VARCHAR(50),
-> EMAILID VARCHAR(100),
-> PHONE_NUMBER VARCHAR(30),
-> HIRE_DATE DATE,
-> JOB_ID VARCHAR(20),
-> SALARY DECIMAL(10, 2),
-> COMMISSION_PCT DECIMAL(5, 2),
-> MANAGER_ID INT,
-> DEPARTMENT_ID INT,
-> FOREIGN KEY (DEPARTMENT_ID, MANAGER_ID)
-> REFERENCES DEPARTMENT2 (DEPARTMENT_ID, MANAGER_ID)
-> );
Query OK, 0 rows affected (0.59 sec)
mysql> DESC EMP0;
+----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| EMPLOYEE_ID | int | NO | PRI | NULL | |
| FIRST_NAME | varchar(50) | YES | | NULL | |
| LAST_NAME | varchar(50) | YES | | NULL | |
| EMAILID | varchar(100) | YES | | NULL | |
| PHONE_NUMBER | varchar(30) | YES | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | varchar(20) | YES | | NULL | |
| SALARY | decimal(10,2) | YES | | NULL | |
| COMMISSION_PCT | decimal(5,2) | YES | | NULL | |
| MANAGER_ID | int | YES | | NULL | |
| DEPARTMENT_ID | int | YES | MUL | NULL | |
+----------------+---------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
mysql>