Consider the tables provided below and answer the following SQL questions

Question:

Consider the tables provided below and answer the following SQL questions:

Table 1: Person.

P_Id	LastName	FirstName	Address	City
1	Jason	Rita	Bella Boulevard	Sydney
2	Smith	Jackson	High Park	Sydney
3	Wagner	James	Richmond	Perth

Table 2: Order.

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	2

 

Answer the following SQL questions:

1. Write insert statements to enter the first two records into Table 1 as shown in the above figure.
2. Display a list that presents the data contained in Table 1 sorted by column 2 in descending order.
3. Display the first name, last name, and city for the person who has placed the order with OrderNo ‘77895’.
4. How many persons belong to Sydney city.

Summary:

In this question, they have given two tables. And we have asked to do certain operations by using SQL commands to get the output.

Explanation:

SQL (Structured Query Language) is a hybrid language used for handling relational database management systems.

SQL is categorized into four types they are:

1)DDL-Data Definition Language.

Commands of data definition language are:

1. Create
2. Alter
3. Rename
4. Drop
5. Truncate

 2) DML-Data Manipulation Language.

Commands for data manipulation language:

1. Insert
2. Select
3. Update
4. Delete

3) DQL-Data Query Language.

Data query language Command:

1. Select

4)DCL-Data Control Language.

Commands of data control language:

1. Grant
2. Revoke

By using these commands we can answer the above questions.

1. From using create command (CREATE Table Tablename) we can create the table person and order_ respectively.
2. And by using the insert command (INSERT into table name values(value1,value2,…..)) insert the values for table person and order_.
3. Next, use the Select command(SELECT * from table name) for performing some actions from the given tables.

Source code:

Code for doing SQL commands.

CREATE TABLE Person
( P_Id int primary key, 
  Lastname char[8], 
  Firstname char[8], 
  Address varchar[15],
  City char[7]
);

CREATE TABLE Order_
(   O_Id int primary key,
    OrderNo int,
    P_Id int, foreign key(P_Id) references Person
);

INSERT INTO Person VALUES (1,'Jason','Rita','Bella Boulevard','Sydney');
INSERT INTO Person VALUES (2,'Smith','Jackson','High Park','Sydney');
INSERT INTO Person VALUES (3,'Wagner','James','Richmond','Perth');

INSERT INTO Order_ VALUES (1,77895,3);
INSERT INTO Order_ VALUES (2,44678,3);
INSERT INTO Order_ VALUES (3,22456,1);
INSERT INTO Order_ VALUES (4,24562,1);
INSERT INTO Order_ VALUES (5,34764,2);

SELECT * FROM Person
order by Lastname desc;

SELECT Firstname, Lastname, City from Person, Order_
where Person.P_Id=Order_.P_Id
AND OrderNo=77895;

SELECT Count(City) from Person
where City='Sydney';

Output:

a)

b)

c)

d)

 

Also, read Prove that the propositional formulas given in the question I, ii, and iii above having the corresponding properties, by means of the semantic tableau.