[go: up one dir, main page]
Scribd
Upload
93 views3 pages

Matts SQL Lab 02

The document describes SQL commands used to create, alter, populate, and drop a SUPPLIER table. It adds and modifies columns, inserts sample data, deletes and updates rows, and describes the table structure before selecting all rows. It then adds a column, drops the table, and shows that no tables exist. Finally, it creates a SUP_PART table with foreign keys to other tables.

Uploaded by

api-545909907
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
93 views3 pages

Matts SQL Lab 02

The document describes SQL commands used to create, alter, populate, and drop a SUPPLIER table. It adds and modifies columns, inserts sample data, deletes and updates rows, and describes the table structure before selecting all rows. It then adds a column, drops the table, and shows that no tables exist. Finally, it creates a SUP_PART table with foreign keys to other tables.

Uploaded by

api-545909907
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Matthew May

MIS331 SQL Lab 2: Changing Tables


USE mjmay_workspace;

CREATE TABLE SUPPLIER (


SID CHAR(2) NOT NULL,
Sname VARCHAR(50),
Status NUMERIC,
Scity VARCHAR(20),
CONSTRAINT PRIMARY KEY (SID)
);

ALTER TABLE SUPPLIER


ADD Sstate CHAR(2);

ALTER TABLE SUPPLIER


MODIFY Status NUMERIC(6,2);

ALTER TABLE SUPPLIER


MODIFY Status INTEGER;

ALTER TABLE SUPPLIER


CHANGE COLUMN Status SupStatus INTEGER;

ALTER TABLE SUPPLIER


CHANGE COLUMN SupStatus Status INTEGER;

INSERT INTO SUPPLIER


VALUES (
'S1',
'Jones',
10,
'Phoenix',
'AZ',
'USA' );

INSERT INTO SUPPLIER


VALUES (
'S2',
'Smith',
20,
'Tucson',
'AZ',
'USA' );

1
Matthew May

INSERT INTO SUPPLIER (SID, Sname, Scity, Status)


VALUES (
'S7',
'Alice',
'Philadelphia',
50 );

INSERT INTO SUPPLIER (SID, Sname, Scity, Status)


VALUES (
'S8',
'Betty',
'Athens',
60 );

DELETE FROM SUPPLIER


WHERE SID='S7';

DELETE FROM SUPPLIER


WHERE SID='S8';

UPDATE SUPPLIER
SET Scity='Phoenix'
WHERE SID='S1';

UPDATE SUPPLIER
SET Status='40'
WHERE SID='S1';

DESCRIBE SUPPLIER;

SELECT * FROM SUPPLIER;

ALTER TABLE SUPPLIER


ADD Scountry VARCHAR(50);

DROP TABLE SUPPLIER;

SHOW TABLES;

CREATE TABLE SUP_PART (


SPID CHAR(4) NOT NULL,
PID CHAR(2) NOT NULL,
SID CHAR(2) NOT NULL,

2
Matthew May

Quantity INTEGER,
CONSTRAINT PRIMARY KEY (SPID),
CONSTRAINT FOREIGN KEY (PID) REFERENCES PART (PID),
CONSTRAINT FOREIGN KEY (SID) REFERENCES SUPPLIER (SID)
);

SHOW TABLES;

You might also like