SQL_Code_Questions.pdf

SQL Code Questions
MySQL Create Table Questions
1. Write a SQL statement to create a simple table countries including columns
country_id,country_name and region_id.


CREATE TABLE countries(
COUNTRY_ID varchar(2),
COUNTRY_NAME varchar(40),
REGION_ID decimal(10,0)
);
DESC countries;




Here is the structure of the table:




2. 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 varchar(2),
COUNTRY_NAME varchar(40),
REGION_ID decimal(10,0)
);




Here is the structure of the table:




SQL Code Questions 1

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


CREATE TABLE IF NOT EXISTS dup_countries
LIKE countries;




Here is the structure of the table:




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


CREATE TABLE IF NOT EXISTS dup_countries
AS SELECT * FROM countries;




Here is the structure of the table:




SQL Code Questions 2

, 5. Write a SQL statement to create a table countries set a constraint NULL.


CREATE TABLE IF NOT EXISTS countries (
COUNTRY_ID varchar(2) NOT NULL,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID decimal(10,0) NOT NULL
);




Here is the structure of the table:




6. 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 IF NOT EXISTS jobs (
JOB_ID varchar(10) NOT NULL ,
JOB_TITLE varchar(35) NOT NULL,
MIN_SALARY decimal(6,0),
MAX_SALARY decimal(6,0)
CHECK(MAX_SALARY<=25000)
);




Here is the structure of the table:




SQL Code Questions 3

, 7. 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.


CREATE TABLE IF NOT EXISTS countries (
COUNTRY_ID varchar(2),
COUNTRY_NAME varchar(40)
CHECK(COUNTRY_NAME IN('Italy','India','China')) ,
REGION_ID decimal(10,0)
);




8. Write a SQL statement to create a table named job_histry 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 '--/--/----'.


CREATE TABLE IF NOT EXISTS job_history (
EMPLOYEE_ID decimal(6,0) NOT NULL,
START_DATE date NOT NULL,
END_DATE date NOT NULL
CHECK (END_DATE LIKE '--/--/----'),
JOB_ID varchar(10) NOT NULL,
DEPARTMENT_ID decimal(4,0) NOT NULL
);




SQL Code Questions 4