DBMS - Insert

  • 1.	Write a SQL statement to insert a record with your own value into the table 
	countries against each columns.

	Here in the following is the structure of the table countries.

	+--------------+---------------+------+-----+---------+-------+
	| Field        | Type          | Null | Key | Default | Extra |
	+--------------+---------------+------+-----+---------+-------+
	| COUNTRY_ID   | varchar(2)    | YES  |     | NULL    |       |
	| COUNTRY_NAME | varchar(40)   | YES  |     | NULL    |       |
	| REGION_ID    | decimal(10,0) | YES  |     | NULL    |       |
	+--------------+---------------+------+-----+---------+-------+

					    						

    	INSERT INTO countries VALUES('C1','India',1001);
						

  • 	
2.	Write a SQL statement to insert one row into the table countries against the column 
	country_id and country_name.

	Here in the following is the structure of the table countries.

	+--------------+---------------+------+-----+---------+-------+
	| Field        | Type          | Null | Key | Default | Extra |
	+--------------+---------------+------+-----+---------+-------+
	| COUNTRY_ID   | varchar(2)    | YES  |     | NULL    |       |
	| COUNTRY_NAME | varchar(40)   | YES  |     | NULL    |       |
	| REGION_ID    | decimal(10,0) | YES  |     | NULL    |       |
	+--------------+---------------+------+-----+---------+-------+	

					    						

    	INSERT INTO countries (country_id,country_name) VALUES('C1','India');
						

  • 3.	Write a SQL statement to create duplicate of countries table named country_new 
	with all structure and data.

	Here in the following is the structure of the table countries.

	+--------------+---------------+------+-----+---------+-------+
	| Field        | Type          | Null | Key | Default | Extra |
	+--------------+---------------+------+-----+---------+-------+
	| COUNTRY_ID   | varchar(2)    | YES  |     | NULL    |       |
	| COUNTRY_NAME | varchar(40)   | YES  |     | NULL    |       |
	| REGION_ID    | decimal(10,0) | YES  |     | NULL    |       |
	+--------------+---------------+------+-----+---------+-------+	

					    						

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

  • 4.	Write a SQL statement to insert NULL values against region_id column for a row 
	of countries table.

					    						

    	INSERT INTO countries (country_id,country_name,region_id) VALUES('C1','India',NULL);
						

  • 5.	Write a SQL statement to insert 3 rows by a single insert statement.

					    						

    	INSERT INTO countries VALUES('C0001','India',1001),
	('C0002','USA',1007),('C0003','UK',1003);
						

  • 6.	Write a SQL statement insert rows from country_new table to countries table.

	Assume that, the countries table is empty.

	+------------+--------------+-----------+
	| COUNTRY_ID | COUNTRY_NAME | REGION_ID |
	+------------+--------------+-----------+
	| C0001      | India        |      1001 |
	| C0002      | USA          |      1007 |
	| C0003      | UK           |      1003 |
	+------------+--------------+-----------+

					    						

    	INSERT INTO countries
	SELECT * FROM country_new;
						

  • 7. 	Write a SQL statement to insert one row in jobs table to ensure that no duplicate 
	value will be entered in the job_id column.

					    						

    	CREATE TABLE IF NOT EXISTS jobs ( 
	JOB_ID integer NOT NULL UNIQUE , 
	JOB_TITLE varchar(35) NOT NULL, 
	MIN_SALARY decimal(6,0)
	);

	INSERT INTO jobs VALUES(1001,'OFFICER',8000);
						

  • 8. 	Write a SQL statement to insert one row in jobs table to ensure that no duplicate value 
	will be entered in the job_id column.

					    						

    	CREATE TABLE IF NOT EXISTS jobs ( 
	JOB_ID integer NOT NULL UNIQUE PRIMARY KEY, 
	JOB_TITLE varchar(35) NOT NULL, 
	MIN_SALARY decimal(6,0)
	);

	INSERT INTO jobs VALUES(1001,'OFFICER',8000);
						

  • 9. 	Write a SQL statement to insert a record into the table countries to ensure that, a 
	country_id and region_id combination will be entered once in the table.

					    						

    	CREATE TABLE IF NOT EXISTS countries ( 
	COUNTRY_ID integer NOT NULL,
	COUNTRY_NAME varchar(40) NOT NULL,
	REGION_ID integer NOT NULL,
	PRIMARY KEY (COUNTRY_ID,REGION_ID)
	);

	INSERT INTO countries VALUES(501,'India',185);
						

  • 10.	Write a SQL statement to insert rows into the table countries in which the value of 
	country_id column will be unique and auto incremented.

					    						

    	CREATE TABLE IF NOT EXISTS countries ( 
	COUNTRY_ID integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
	COUNTRY_NAME varchar(40) NOT NULL,
	REGION_ID integer NOT NULL
	);

	INSERT INTO countries(COUNTRY_NAME,REGION_ID) VALUES('India',185);