Connecting to the Database With SQL

Problem 1: - Connecting to the Database and First Steps With SQL

Part 1

Part 2

Step 1

{'/*--------Relation_types table-----*/'}

CREATE TABLE Relation_types

(

Relation_id INT PRIMARY KEY,

Relationship CHAR(20)

);

{'/*-------Contacts table-----*/'}

CREATE TABLE Contacts

(

Email CHAR(30) PRIMARY KEY,

Name CHAR(20),

Phone_work CHAR(12),

Phone_mobile CHAR(12),

Relation_id INT,

FOREIGN KEY (Relation_id) REFERENCES Relation_types(Relation_id)

);

{'/*-----DESCRIBE------*/'}

DESCRIBE Relation_types;

DESCRIBE Contacts;

Step 2

{'/*-----insert data into Relatin_types-----*/'}

INSERT INTO Relation_types VALUES

(1,'Family'),

(2,'Friend'),

(3,'Colleague');

{'/*-------insert data into Contacts ---------*/''}

INSERT INTO Contacts VALUES

('Jhone123@gmail.com','Jhon Smith','0987890987','0989878767',1),

('Robin123@gmail.com','Robin Smith','5656456654','0987654321',2),

('Meena123@gmail.com','Meena Caan','5656456651','0987654322',3),

('Reena123@gmail.com','Reena Caan','5656456652','0987654323',1),

('Rohit123@gmail.com','Rohit Smith','5656456653','0987654324',2);

Step 3

SELECT *FROM Relation_types;

SELECT *FROM Contacts;

SELECT NAME as "Person Name", EMAIL as "Email address" FROM Contacts;

Step 4

UPDATE Contacts SET

Name='Hema Smith', Phone_mobile='0987876565',

Email='Hema123@gmail.com', Relation_id=3

where Email='Jhone123@gmail.com';

select *from Contacts;

Step 5

SELECT Contacts.Name, Contacts.Phone_mobile

FROM Contacts Join Relation_types

WHERE Contacts.Relation_id=Relation_types.Relation_id

AND Relation_types.Relationship='Friend';