Make Twenty (20) SQL questions including create table, insert, update, delete, join, string
operation, aggregate function with group by
Answers
Explanation:
Inserting a New Record
Problem
You want to insert a new record into a table. For example, you want to insert a new record into the DEPT table. The value for DEPTNO should be 50, DNAME should be “PROGRAMMING”, and LOC should be “BALTIMORE”.
Solution
Use the INSERT statement with the VALUES clause to insert one row at a time:
insert into dept (deptno,dname,loc)
values (50,'PROGRAMMING','BALTIMORE')
For DB2 and MySQL you have the option of inserting one row at a time or multiple rows at a time by including multiple VALUES lists:
/* multi row insert */
insert into dept (deptno,dname,loc)
values (1,'A','B'),
(2,'B','C')
Discussion
The INSERT statement allows you to create new rows in database tables. The syntax for inserting a single row is consistent across all database brands.
As a shortcut, you can omit the column list in an INSERT statement:
insert into dept
values (50,'PROGRAMMING','BALTIMORE')
However, if you do not list your target columns, you must insert into all of the columns in the table, and be mindful of the order of the values in the VALUES list; you must supply values in the same order in which the database displays columns in response to a SELECT * query.
4.2. Inserting Default Values
Problem
A table can be defined to take default values for specific columns. You want to insert a row of default values without having to specify those values. Consider the following table:
create table D (id integer default 0)
You want to insert zero without explicitly specifying zero in the values list of an INSERT statement. You want to explicitly insert the default, whatever that default is.
Solution
All brands support use of the DEFAULT keyword as a way of explicitly specifying the default value for a column. Some brands provide additional ways to solve the problem.
The following example illustrates the use of the DEFAULT keyword:
insert into D values (default)
You may also explicitly specify the column name, which you’ll need to do anytime you are not inserting into all columns of a table:
insert into D (id) val
Copying Rows from One Table into Another
Problem
You want to copy rows from one table to another by using a query. The query may be complex or simple, but ultimately you want the result to be inserted into another table. For example, you want to copy rows from the DEPT table to the DEPT_EAST table. The DEPT_EAST table has already been created with the same structure (same columns and data types) as DEPT and is currently empty.
Solution
Use the INSERT statement followed by a query to produce the rows you want:
1 insert into dept_east (deptno,dname,loc)
2 select deptno,dname,loc
3 from dept
4 where loc in ( 'NEW YORK','BOSTON' )
Discussion
Simply follow the INSERT statement with a query that returns the desired rows. If you want to copy all rows from the source table, exclude the WHERE clause from the query. Like a regular insert, you do not have to explicitly specify which columns you are inserting into. But if you do not specify your target columns, you must insert into all of the table’s columns, and you must be mindful of the order of the values in the SELECT list as described earlier in “Inserting a New Record.”
4.5. Copying a Table Definition
Problem
You want to create a new table having the same set of columns as an existing table. For example, you want to create a copy of the DEPT table and call it DEPT_2. You do not want to copy the rows, only the column structure of the table.
Solution
DB2
Use the LIKE clause with the CREATE TABLE command:
create table dept_2 like dept
Oracle, MySQL, and PostgreSQL
Use the CREATE TABLE command with a subquery that returns no rows:
1 create table dept_2
2 as
3 select *
4 from dept
5 where 1 = 0
Pls check whether it is correct
Thank you pls vote me and thank me