NEED HELP IN INSERT STATEMENTS FOR THE TABLES CAN YOU PLEASE ADD SOME INSERT STA

Question

NEED HELP IN INSERT STATEMENTS FOR THE TABLES

CAN YOU PLEASE ADD SOME INSERT STATEMENTS FOR A COMPANY SCHEMA SCRIPT. PLEASE ADN THANK YOU

DROP TABLE dependent;
DROP TABLE works_on;
DROP TABLE project;
DROP TABLE dept_locations;
DROP TABLE department;
DROP TABLE employee;

CREATE TABLE employee (
fname varchar(15) not null,
minit varchar(1),
lname varchar(15) not null,
ssn char(9),
bdate date,
address varchar(50),
sex char,
salary numeric(10,2),
superssn char(9),
dno numeric,
primary key (ssn),
foreign key (superssn) references employee(ssn)

);

CREATE TABLE department (
dname varchar(25) not null,
dnumber numeric,
mgrssn char(9) not null,
mgrstartdate date,
primary key (dnumber),
unique (dname),
foreign key (mgrssn) references employee(ssn)
);

ALTER TABLE employee ADD foreign key (dno) references department(dnumber);

CREATE TABLE dept_locations (
dnumber numeric,
dlocation varchar(15),
primary key (dnumber,dlocation),
foreign key (dnumber) references department(dnumber)
);

CREATE TABLE project (
pname varchar(25) not null,
pnumber numeric,
plocation varchar(15),
dnum numeric not null,
primary key (pnumber),
unique (pname),
foreign key (dnum) references department(dnumber)
);

CREATE TABLE works_on (
essn char(9),
pno numeric,
hours numeric(4,1),
primary key (essn,pno),
foreign key (essn) references employee(ssn),
foreign key (pno) references project(pnumber)
);

CREATE TABLE dependent (
essn char(9),
dependent_name varchar(15),
sex char,
bdate date,
relationship varchar(8),
primary key (essn,dependent_name),
foreign key (essn) references employee(ssn)
);

Explanation / Answer

The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways.

1.The first way specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

2.Insert Data Only in Specified Columns

It is also possible to only insert data in specific columns.

The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):

Example

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

This is how a typical insert statement looks like,

I will give you some examples how to insert data in various forms in various situations it will helps you to understand better in inserting data into the tables.

Example1:

CREATE TABLE table1 (user VARCHAR(20) PRIMARY KEY, age INT);
CREATE TABLE table2 (user VARCHAR(20) PRIMARY KEY, os VARCHAR(20));

INSERT INTO table1 (user,age) VALUES ('fred',20);
INSERT INTO table1 (user,age) VALUES ('mary',30);
INSERT INTO table2 (user,os) VALUES ('fred','FreeBSD');
INSERT INTO table2 (user,os) VALUES ('mary','Linux');

Example2:

If you need to duplicate a row in same table with unique keys you can do that

1) Obtain the last unique key
2) Sum 1 to last key
3) execute INSERT SELECT ON DUPLICATE KEY

Example
1) $lastids=mysql_fetch_array(mysql_query("SELECT Id_Inmueble FROM inmueble WHERE 1 ORDER BY Id_Inmueble DESC"));
2) $lastid=$lastids["Id_Inmueble"]+1;
3) mysql_query("INSERT INTO inmueble SELECT * FROM inmueble WHERE Id_Inmueble=".$Id." ON DUPLICATE KEY UPDATE Id_Inmueble=".$ultimoid) or die(mysql_error());

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.