Project 1: Language Identification and Validation Your task for Project 1 is to

Question

Project 1: Language Identification and Validation

Your task for Project 1 is to create a language that will allow a user to manage, store, and query user data for a corporation. You will be creating a system similar to SQL. This project will focus on creating the basic language parsing mechanism to recognize valid (and invalid) phrases in the language. Below you will find a complete sampling of the valid statements in the language. Note that some of the statements can be nested together. You should only print the following:

You must implement functionality for both the execute command as well as the exit command.

If it is a valid statement is should print “VALID select Statement” (where select is whatever the type is)

If it is not a valid statement it should print “INVALID select Syntax” (where select is whatever the type is)

If it doesn’t have a type (complete garbage) then print “INVALID SYNTAX”

CLARIFICATIONS:

TABLE_NAME must be alphabetic only (no reserved words and no special symbols)

COLUMN_NAME must be alphabetic only (no reserved words and no special symbols)

Commands can be multi-lined as well as multiple statements on a single line

(Underline denotes optional items)

Functionality:

create table TABLE_NAME (

COLUMN_NAME TYPE,

COLUMN_NAME TYPE,

COLUMN_NAME TYPE,

....

);

Note: TYPE is the type of the data stored in the field of the table. The valid types are: integer, float, boolean, char, char(n) where n is an integer (fixed length string).

drop table TABLE_NAME;

alter table TABLE_NAME add COLUMN_NAME TYPE;

alter table TABLE_NAME drop COLUMN_NAME;

alter table TABLE_NAME alter COLUMN_NAME TYPE;

select COLUMN_NAME_LIST from TABLE_EXPRESSION;

NOTE: COLUMN_NAME_LIST can be the following:

COLUMN_NAME, COLUMN_NAME, ...

* denotes ALL columns. EX. select * from Student;

NOTE: TABLE_EXPRESSION can be the following:

TABLE_NAME

A nested SELECT_STATEMENT in parenthesis.

TABLE_EXPRESSION natural join TABLE_EXPRESSION

TABLE_EXPRESSION inner join TABLE_EXPRESSION on COLUMN_NAME

select distinct COLUMN_NAME, COLUMN_NAME, … from TABLE_EXPRESSION;

select COLUMN_NAME_LIST from TABLE_EXPRESSION

where BOOLEAN_EXPRESSION;

NOTE: BOOLEAN_EXPRESSION can contain the following:

COLUMN_NAME COMPARATOR IDENTIFIER

IDENTIFIER is a VALUE

COMPARATOR is one of the following: =, <, >, >=, <=.

BOOLEAN_EXPRESSION LOGIC_OPERATOR BOOLEAN_EXPRESSION

LOGIC_OPERATOR is either and or or.

And has higher precedence

not BOOLEAN_EXPRESSION

insert into TABLE_NAME (COLUMN_NAME_LIST) values (VALUES_LIST);

NOTE: COLUMN_NAME_LIST length must match VALUES_LIST length, but Type doesn’t need to be checked for this project.

update TABLE_NAME set COLUMN_NAME = VALUE where BOOLEAN_EXPRESSION;

delete from TABLE_NAME where BOOLEAN_EXPRESSION;

execute from FILE_NAME;

exit;

Explanation / Answer

To create a new table,
eg: customer,

CREATE TABLE customer (
   customerId int NOT NULL AUTO_INCREMENT,
   fName varchar(42) NOT NULL,
   lName varchar(42) NOT NULL,
   phoneNum DECIMAL(20),
   address varchar(122),
   emailId varchar(200) NOT NULL,
   customerType tinyint(1) NOT NULL,
   PRIMARY KEY (customerId)
);

In order to delete customer table,
DROP TABLE customer;

In order to add new column in the existing customer table,
ALTER TABLE customer ADD zipcode int(10) NOT NULL;

To drop a cloumn in the existing customer table,
ALTER TABLE customer DROP customerType;

To modify the column in the existing customer table,
ALTER TABLE customer ALTER COLUMN emailId varchar(100) NOT NULL;

To select table with some specified column/field

SELECT customerId, fName, lName, phoneNum, address FROM customer ORDER BY customerId DESC;

O/P:

customerId   fName   lName   phoneNum   address
5   Paul   A   8654230147   test address5
4   Rita   U   7412589303   test address4
3   Harry   N   9632156908   test address3
2   Sally   T   7898653250   test address2
1   Thomas   S   8369452101   test address1

INSERT INTO
customer
VALUES
(1, 'Thomas', 'S', 8369452101, 'test address1', 'test1@test.com', 623510),
(2, 'Sally', 'T', 7898653250, 'test address2', 'test2@test.com', 623511),
(3, 'Harry', 'N', 9632156908, 'test address3', 'test3@test.com', 623512),
(4, 'Rita','U', 7412589303, 'test address4', 'test4@test.com', 623513),
(5, 'Paul','A', 8654230147, 'test address5', 'test5@test.com', 623514);

Create another table:
CREATE TABLE product (
   prodId int NOT NULL AUTO_INCREMENT,
   name varchar(255) NOT NULL,
   price decimal(6,2) NOT NULL,
   PRIMARY KEY (prodId)
);

INSERT INTO
product
VALUES
(1, 'onion', 73.50),
(2, 'tomato',350.00),
(3, 'mushroom', 400.00),
(4, 'cheese', 300.00),
(5, 'chips', 300.00),
(6, 'chips', 325.50);

Nested select:
SELECT name FROM product
WHERE price >
     (SELECT price FROM product
      WHERE name='cheese')

It will return the data that the product price is greater than cheese price. Which means the price should be greater than 350.00
O/P:
name
tomato
mushroom

SELECT DISTINCT name FROM product ORDER BY name;
It will return no duplicates value. In product table we have 2 entries for chips. So the result would be,
O/P:
name
cheese
chips
mushroom
onion
tomato

select COLUMN_NAME_LIST from TABLE_EXPRESSION
where BOOLEAN_EXPRESSION;

SELECT customerId, fName, lName, phoneNum, emailId, address FROM customer WHERE fname = "Sally" AND (emailId like '%test%' OR phoneNum IS NOT NULL)
O/P:
customerId   fName   lName   phoneNum   emailId   address
2   Sally   T   7898653250   test2@test.com   test address2

update TABLE_NAME set COLUMN_NAME = VALUE where BOOLEAN_EXPRESSION;
UPDATE product SET price = 125.50 WHERE name = 'onion', It will update the price 125.20 instead of 73.50 for onion in product table, so the result will be,
O/P:
prodId   name   price
1   onion   125.5
2   tomato   350
3   mushroom   400
4   cheese   300
5   chips   300
6   chips   325.5

delete from TABLE_NAME where BOOLEAN_EXPRESSION;

DELETE FROM product WHERE prodId = 6; It will delete the row 6 from product table, hence the result would be,
O/P:
prodId   name   price
1   onion   125.5
2   tomato   350
3   mushroom   400
4   cheese   300
5   chips   300

TABLE_EXPRESSION natural join TABLE_EXPRESSION

SELECT c1.fName, c1.lName
FROM customer as c1
INNER JOIN customer as c2 ON c1.fname = c2.fName
WHERE c1.zipcode = 623511;

Sample Output:
fName   lName
Sally   T

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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