In-class Exercise W13 Write CQL Statements  for the following: submit (script in

Subject:Computer SciencePrice: Bought3

Share With

In-class Exercise W13

Write CQL Statements  for the following:
submit (script in .txt + screenshot of the results for each test case in a .pdf or .docx format)

 

Use keyspace created in previous lab or create a keyspace called: "Week13_Lab_studentname"
 

Question 1 (50 points)

 

• Create a proj4 table with 3 columns:

pname varchar, 
              startdate (timestamp), 
              emps map<text, text>, here the key for emps is ename, and value is dname

 

• Insert into proj4 the following values

pname, startdate, emps

'UMBC Library','2019-01-11',{'Jeff':'IT','Emma':'IT'});

'UMBC Student Record','2019-03-01',{'Jeff':'IT','Bob':'IT'});

 

• Return names of employees working on UMBC Library project and belongs to IT department

 

• Create a table emp5 with the following columns:

dname varchar,

ename varchar,

hiredate timestamp,

salary varint,

proj list<text>

 

• Insert into emp5 the following rows:

Ename, Dname, Hiredate, Salary, proj

'Jeff', 'IT', '2019-02-03', 60000, ['UMBC Library', 'UMBC Student Record']

Susan', 'HR', '2016-01-03', 70000

'Emma', 'IT', '2017-11-03', 80000, ['UMBC Library']

'Bob', 'IT', '2018-01-03', 70000, 'UMBC Student Record']

• Return names of employees at 'IT' department and works on 'UMBC Student Record' project

 

 

Question 2 ( 50 points)

• Suppose in relational database you have created 3 tables: 

cust: cid (int), cname (varchar)

prod: pid(int), pname(varchar), price(float)

transaction: cid(int), pid(int), tdate(timestamp)

Users want to return names of products and transaction date given cname

Or return names of customers and tdate given pname

 

• Create two tables in Cassandra storing all these information and support these queries
• Insert into this created tables the following data:

Row1:

cid = 1, cname = Jeff, 2 transactions

Pid = 1, pname = iPhone 10, price = 1000, tdate = '2019-02-01'

Pid = 2, pname = Harry Potter, price = 100, tdate = '2019-03-01'

 

Row 2:

 cid = 2, cname = Alice, 2 transactions:

Pid = 1, pname = iphone 10, price = 1000, tdate = '2019-01-01'

Pid = 5, pname = Levi Jeans, price = 60, tdate = '2019-03-01'

 

• Execute the following queries

-Return names of products and transaction date for customer 'Jeff'

-Return names of customers and the date they bought the product Harry Potter