Queries with Tables & Constraints

Queries with Tables & Constraints

MCQ - 1

MCQ - 2

MCQ - 3

Ans :a

SQL Query - 1

CREATE TABLE People(

PID INT PRIMARY KEY,

LastName VARCHAR(12),

FirstName VARCHAR(15) NOT NULL,

Address VARCHAR(50),

City VARCHAR(20) );

SELECT table_name, column_name, data_type

FROM information_schema.columns

WHERE table_name = 'People'

ORDER BY column_name;

SQL Query - 2

CREATE TABLE Patients(

Patient_id INT PRIMARY KEY,

Patient_title CHAR(10) NOT NULL,

Patient_name CHAR(20) NOT NULL,

admit_date DATE);

SELECT table_name, column_name, data_type

FROM information_schema.columns

WHERE table_name = 'Patients'

ORDER BY column_name;

SQL Query - 3

CREATE TABLE customer(

ID INT PRIMARY KEY,

Name VARCHAR(50) NOT NULL,

City VARCHAR(50) NOT NULL);

CREATE TABLE contacts(

ID INT PRIMARY KEY,

Customer_Id INT,

Customer_Info VARCHAR(50) NOT NULL,

Type VARCHAR(50) NOT NULL,

FOREIGN KEY(Customer_Id) REFERENCES Customer(ID));

DESC customer;

DESC contacts;

SQL Query - 4

CREATE TABLE users(

id INT PRIMARY KEY,

full_name VARCHAR(20) NOT NULL,

enabled CHAR(10) NOT NULL,

last_login DATE );

SELECT table_name, column_name, data_type

FROM information_schema.columns

WHERE table_name = 'users';

CREATE TABLE addresses(

user_id INT PRIMARY KEY,

street VARCHAR(50) NOT NULL,

city VARCHAR(25) NOT NULL,

state VARCHAR(50) NOT NULL,

FOREIGN KEY (user_id) REFERENCES users(id));

SELECT table_name, column_name, data_type

FROM information_schema.columns

WHERE table_name = 'addresses';

SQL Query - 5

CREATE TABLE books(

id INT PRIMARY KEY,

title VARCHAR(50) NOT NULL,

author VARCHAR(25) NOT NULL,

published_date TIMESTAMP NOT NULL,

isbn CHAR(12) UNIQUE);

SELECT table_name, column_name, data_type

FROM information_schema.columns

WHERE table_name = 'books';

CREATE TABLE reviews(

id INT PRIMARY KEY ,

book_id INT NOT NULL,

reviewer_name VARCHAR(225),

content VARCHAR(225),

rating INT,

published_date TIMESTAMP,

FOREIGN KEY(book_id) REFERENCES books(id));

SELECT table_name, column_name, data_type

FROM information_schema.columns

WHERE table_name = 'reviews';

SQL Query - 6

ALTER TABLE members ADD cc_number VARCHAR(50);

SELECT table_name, column_name, data_type

FROM information_schema.columns

WHERE table_name = 'members'

ORDER BY column_name;

SQL Query - 7

ALTER TABLE Bank CHANGE COLUMN person_id Pid VARCHAR(50);

DESC Bank;

SQL Query - 8

ALTER TABLE members DROP member_dob;

SELECT table_name, column_name, data_type

FROM information_schema.columns

WHERE table_name = 'members'

ORDER BY column_name;

SQL Query - 9

ALTER TABLE consumers RENAME Consumer_Data;

SELECT table_name, column_name, data_type

FROM information_schema.columns

WHERE table_name = 'Consumer_Data'

ORDER BY column_name;

SQL Query - 10

ALTER TABLE customer DROP last_login, CHANGE COLUMN full_name customer_name VARCHAR(30);

DESC customer;