MYSQL-Exercise-

Write a SQL statement to create a simple table countries including columns country_id,country_name and region_id.

CREATE TABLE countries (country_id int, country_name Varchar(255), region_id int )
Write a SQL statement to create a simple table countries including columns country_id,country_name and region_id which is already exists.

CREATE TABLE IF NOT EXISTS countries (country_id int, country_name Varchar(255), region_id int )
Write a SQL statement to create the structure of a table dup_countries similar to countries.

CREATE TABLE dup_countries as SELECT * FROM countries
Write a SQL statement to create a duplicate copy of countries table including structure and data by name dup_countries.

CREATE TABLE dup_countries LIKE countries
Write a SQL statement to create a table countries set a constraint NOT NULL (all the fields should be not null).

CREATE TABLE countries2 ( name int(10) NOT NULL, last_name varchar(255) NOT NULL )
Write a 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.

CREATE TABLE jobs ( job_id int, job_title varchar(255), min_salary float , max_salary float ) SELECT * FROM jobs LIMIT 25000
Write a SQL statement to create a table named countries including columns country_id, country_name and region_id and make sure that no countries except Italy, India and China will be entered in the table.

INSERT INTO countries (country_name) VALUES ('india') INSERT INTO countries (country_name) VALUES ('italy') INSERT INTO countries (country_name) VALUES ('china')
Write a SQL statement to create a table named job_history including columns employee_id, start_date, end_date, job_id and department_id and make sure that the value against column end_date will be entered at the time of insertion to the format like '--/--/----'.
Write a SQL statement to create a table named countries including columns country_id,country_name and region_id and make sure that no duplicate data against column country_id will be allowed at the time of insertion.

CREATE TABLE countries ( country_ID int NOT NULL UNIQUE, country_name varchar(255), region_id int );
Write a 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.

CREATE TABLE jobs ( job_ID int, job_title varchar(255) DEFAULT '', min_salary int DEFAULT 8000, max_salary int );
Write a SQL statement to create a table named countries including columns country_id, country_name and region_id and make sure that the country_id column will be a key field which will not contain any duplicate data at the time of insertion.

CREATE TABLE countries ( country_id int NOT NULL, country_name varchar(255), region_id int , PRIMARY KEY (country_id) );
Write a SQL statement to create a table countries including columns country_id, country_name and region_id and make sure that the column country_id will be unique and store an auto incremented value.

CREATE TABLE countries ( country_id int NOT NULL, country_name varchar(255), region_id int, PRIMARY KEY (country_id) );

Write a SQL statement to create a table countries including columns country_id, country_name and region_id and make sure that the combination of columns country_id and region_id will be unique.

CREATE TABLE countries ( country_id int NOT NULL UNIQUE, country_name varchar(255), region_id int NOT NULL UNIQUE )
Write a SQL statement to create a table job_history including columns employee_id, start_date, end_date, job_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 column job_id contain only those values which are exists in the jobs table.

Here is the structure of the table jobs;

Field	Type	Null	Key	Default
JOB_ID	varchar(10)	NO	PRI
JOB_TITLE	varchar(35)	NO		NULL
MIN_SALARY	decimal(6,0)	YES		NULL
MAX_SALARY	decimal(6,0)	YES		NULL

==================================== CREATE TABLE jobs ( job_ID varchar(10) NOT NULL, job_title varchar(35) NOT NULL, min_salary decimal(6,0) NULL, max_salary decimal(6,0) NULL, PRIMARY KEY (job_ID) );

CREATE TABLE job_history (

employee_id int NOT NULL UNIQUE, start_date date , end_date date, job_id varchar(10), FOREIGN KEY (job_id) REFERENCES jobs(job_id) )

Write a 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.

Assume the structure of departments table below.

Field	Type	Null	Key	Default
DEPARTMENT_ID	decimal(4,0)	NO	PRI	0
DEPARTMENT_NAME	varchar(30)	NO		NULL
MANAGER_ID	decimal(6,0)	NO	PRI	0
LOCATION_ID	decimal(4,0)	YES		NULL

===================================== CREATE TABLE department( department_id decimal(4,0) NOT NULL UNIQUE DEFAULT 0, deparment_name varchar(30) NOT NULL, manger_id decimal(6,0) NOT NULL UNIQUE DEFAULT 0, location_id decimal(4,0) NULL , PRIMARY KEY(department_id,manger_id) )

CREATE TABLE employees ( employee_id int NOT NULL UNIQUE, first_name varchar(30), last_name varchar(30), email varchar(30), phone_number int, hire_date date, job_id varchar(10) NOT NULL, salary int, commission decimal(6,0), manger_id varchar(10) NOT NULL , department_id decimal(4,0) NOT NULL UNIQUE, FOREIGN KEY (department_id) REFERENCES department(department_id) )ENGINE=InnoDB;

Write a 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 column department_id, reference by the column department_id of departments table, can contain only those values which are exists in the departments table and another foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which are exists in the jobs table. The InnoDB Engine have been used to create the tables.

"A foreign key constraint is not required merely to join two tables. For storage engines other than InnoDB, it is possible when defining a column to use a REFERENCES tbl_name(col_name) clause, which has no actual effect, and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table." - Reference dev.mysql.com

Assume that the structure of two tables departments and jobs.

Field	Type	Null	Key	Default
DEPARTMENT_ID	decimal(4,0)	NO	PRI	0
DEPARTMENT_NAME	varchar(30)	NO		NULL
MANAGER_ID	decimal(6,0)	YES		NULL
LOCATION_ID	decimal(4,0)	YES		NULL

Field	Type	Null	Key	Default
JOB_ID	varchar(10)	NO	PRI
JOB_TITLE	varchar(35)	NO		NULL
MIN_SALARY	decimal(6,0)	YES		NULL
MAX_SALARY	decimal(6,0)	YES		NULL

========================== ALTER TABLE jobs ADD FOREIGN KEY (job_id) REFERENCES jobs(job_id);

Write a SQL statement to create a table employees including columns employee_id, first_name, last_name, job_id, salary and make sure that, the employee_id column does not contain any duplicate value at the time of insertion, and the foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which are exists in the jobs table. The InnoDB Engine have been used to create the tables. The specialty of the statement is that, The ON UPDATE CASCADE action allows you to perform cross-table update and ON DELETE RESTRICT action reject the deletion. The default action is ON DELETE RESTRICT.

Assume that the structure of the table jobs and InnoDB Engine have been used to create the table jobs.

JOB_ID integer NOT NULL UNIQUE PRIMARY KEY,
JOB_TITLE varchar(35) NOT NULL DEFAULT ' ',
MIN_SALARY decimal(6,0) DEFAULT 8000,
MAX_SALARY decimal(6,0) DEFAULT NULL
)ENGINE=InnoDB;

Field	Type	Null	Key	Default
JOB_ID	int(11)	NO	PRI	NULL
JOB_TITLE	varchar(35)	NO
MIN_SALARY	decimal(6,0)	YES		8000
MAX_SALARY	decimal(6,0)	YES		NULL

CREATE TABLE employees( employee_id int NOT NULL UNIQUE, first_name varchar(30), last_name varchar(30), email varchar(30), job_id varchar(10), FOREIGN KEY (job_id) REFERENCES jobs(job_id) ON DELETE RESTRICT )ENGINE=InnoDB

Write a SQL statement to create a table employees including columns employee_id, first_name, last_name, job_id, salary and make sure that, the employee_id column does not contain any duplicate value at the time of insertion, and the foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which are exists in the jobs table. The InnoDB Engine have been used to create the tables. The specialty of the statement is that, The ON DELETE CASCADE that lets you allow to delete records in the employees(child) table that refer to a record in the jobs(parent) table when the record in the parent table is deleted and the ON UPDATE RESTRICT actions reject any updates.

Assume that the structure of the table jobs and InnoDB Engine have been used to create the table jobs.

JOB_ID integer NOT NULL UNIQUE PRIMARY KEY,
JOB_TITLE varchar(35) NOT NULL DEFAULT ' ',
MIN_SALARY decimal(6,0) DEFAULT 8000,
MAX_SALARY decimal(6,0) DEFAULT NULL
)ENGINE=InnoDB;

Field	Type	Null	Key	Default
JOB_ID	int(11)	NO	PRI	NULL
JOB_TITLE	varchar(35)	NO
MIN_SALARY	decimal(6,0)	YES		8000
MAX_SALARY	decimal(6,0)	YES		NULL

CREATE TABLE jobs ( job_ID varchar(10) NOT NULL, job_title varchar(35) NOT NULL, min_salary decimal(6,0) NULL, max_salary decimal(6,0) NULL, PRIMARY KEY (job_ID), FOREIGN KEY (job_id) REFERENCES employees(job_id) ON UPDATE RESTRICT ); 19. Write a SQL statement to create a table employees including columns employee_id, first_name, last_name, job_id, salary and make sure that, the employee_id column does not contain any duplicate value at the time of insertion, and the foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which are exists in the jobs table. The InnoDB Engine have been used to create the tables. The specialty of the statement is that, The ON DELETE SET NULL action will set the foreign key column values in the child table(employees) to NULL when the record in the parent table(jobs) is deleted, with a condition that the foreign key column in the child table must accept NULL values and the ON UPDATE SET NULL action resets the values in the rows in the child table(employees) to NULL values when the rows in the parent table(jobs) are updated.

Assume that the structure of two table jobs and InnoDB Engine have been used to create the table jobs.

JOB_ID integer NOT NULL UNIQUE PRIMARY KEY,
JOB_TITLE varchar(35) NOT NULL DEFAULT ' ',
MIN_SALARY decimal(6,0) DEFAULT 8000,
MAX_SALARY decimal(6,0) DEFAULT NULL
)ENGINE=InnoDB;

Field	Type	Null	Key	Default
JOB_ID	int(11)	NO	PRI	NULL
JOB_TITLE	varchar(35)	NO
MIN_SALARY	decimal(6,0)	YES		8000
MAX_SALARY	decimal(6,0)	YES		NULL

Write a SQL statement to create a table employees including columns employee_id, first_name, last_name, job_id, salary and make sure that, the employee_id column does not contain any duplicate value at the time of insertion, and the foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which are exists in the jobs table. The InnoDB Engine have been used to create the tables. The specialty of the statement is that, The ON DELETE NO ACTION and the ON UPDATE NO ACTION actions will reject the deletion and any updates.

Assume that the structure of two table jobs and InnoDB Engine have been used to create the table jobs.

JOB_ID integer NOT NULL UNIQUE PRIMARY KEY,
JOB_TITLE varchar(35) NOT NULL DEFAULT ' ',
MIN_SALARY decimal(6,0) DEFAULT 8000,
MAX_SALARY decimal(6,0) DEFAULT NULL
)ENGINE=InnoDB;

Field	Type	Null	Key	Default
JOB_ID	int(11)	NO	PRI	NULL
JOB_TITLE	varchar(35)	NO
MIN_SALARY	decimal(6,0)	YES		8000
MAX_SALARY	decimal(6,0)	YES		NULL

Mohammed-Aljasem/My-SQL-Exercise

MYSQL-Exercise-

Write a SQL statement to create a simple table countries including columns country_id,country_name and region_id.

Write a SQL statement to create a simple table countries including columns country_id,country_name and region_id which is already exists.

Write a SQL statement to create the structure of a table dup_countries similar to countries.

Write a SQL statement to create a duplicate copy of countries table including structure and data by name dup_countries.

Write a SQL statement to create a table countries set a constraint NOT NULL (all the fields should be not null).

Write a 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.

Write a SQL statement to create a table named countries including columns country_id, country_name and region_id and make sure that no countries except Italy, India and China will be entered in the table.

Write a SQL statement to create a table named countries including columns country_id,country_name and region_id and make sure that no duplicate data against column country_id will be allowed at the time of insertion.

Write a SQL statement to create a table named countries including columns country_id, country_name and region_id and make sure that the country_id column will be a key field which will not contain any duplicate data at the time of insertion.

Write a SQL statement to create a table countries including columns country_id, country_name and region_id and make sure that the column country_id will be unique and store an auto incremented value.

Write a SQL statement to create a table countries including columns country_id, country_name and region_id and make sure that the combination of columns country_id and region_id will be unique.