Computer Science
A departmental store "ABC" is considering maintaining their inventory using SQL to store data and maintain basic transactions. As a database manager, Mehak has to create two tables as Customer & Transaction:
Table: CUSTOMER
| CNo | CNAME | ADDRESS |
|---|---|---|
| 101 | Richa Jain | Delhi |
| 102 | Surbhi Sinha | Chennai |
| 103 | Lisa Thomas | Bengaluru |
| 104 | Imran Ali | Delhi |
| 105 | Roshan Singh | Chennai |
Table: TRANSACTION
| Dept | CNO | AMOUNT | TYPE | DOT |
|---|---|---|---|---|
| T001 | 101 | 1500 | Credit | 2017-11-23 |
| T002 | 103 | 2000 | Debit | 2017-05-12 |
| T003 | 102 | 3000 | Credit | 2017-06-10 |
| 1004 | 103 | 12000 | Credit | 2017-09-12 |
| T005 | 101 | 1000 | Debit | 2017-09-05 |
(i) Identify the attribute best suited to be declared as a Primary key in customer table.
(ii) Help Mehak to display details of all transactions of TYPE Credit from Table TRANSACTION.
(iii) Mehak wants to display all CNO, CNAME and DOT (date of transaction) of those CUSTOMERS from tables CUSTOMER and TRANSACTION who have done transactions more than or equal to 2000 for the Balance Sheet.
(iv) Mehak wants to display the last date of transaction (DOT) from the table TRANSACTION for the customer having CNO as 103. Which command will she use?
SQL Joins & Grouping
3 Likes
Answer
(i) The attribute best suited to be declared as a Primary Key in the CUSTOMER table is CNo (Customer Number). It uniquely identifies each customer in the table.
(ii)
SELECT * FROM TRANSACTION
WHERE TYPE = "Credit";
Output
+------+-----+--------+--------+------------+
| DEPT | CNO | AMOUNT | TYPE | DOT |
+------+-----+--------+--------+------------+
| T001 | 101 | 1500 | Credit | 2017-11-23 |
| T003 | 102 | 3000 | Credit | 2017-06-10 |
| T004 | 103 | 12000 | Credit | 2017-09-12 |
+------+-----+--------+--------+------------+
(iii)
SELECT C.CNO, C.CNAME, T.DOT
FROM CUSTOMER C, TRANSACTION T
WHERE C.CNO = T.CNO AND T.AMOUNT >= 2000;
Output
+-----+--------------+------------+
| CNO | CNAME | DOT |
+-----+--------------+------------+
| 103 | LISA THOMAS | 2017-05-12 |
| 102 | SURBHI SINHA | 2017-06-10 |
| 103 | LISA THOMAS | 2017-09-12 |
+-----+--------------+------------+
(iv) Mehak will use "MAX()" function to display the last date of transaction (DOT) from the table TRANSACTION for the customer having CNO as 103.
SELECT MAX(DOT) AS Last_Transaction_Date
FROM TRANSACTION
WHERE CNO = 103;
Output
+-----------------------+
| Last_Transaction_Date |
+-----------------------+
| 2017-09-12 |
+-----------------------+
Answered By
2 Likes
Related Questions
Write a Python code to insert a new record as per given table Student (No, Name, Age, Department, Fee, Sex) using MySQL connectivity.
A binary file "salary.Dat" has structure [employee id, employee name, salary]. Write a function countrec() in Python that would read contents of the file "salary.Dat" and display the details of those employees whose salary is above 20000.
Sonia was writing a code to insert multiple rows in the cust.csv file. By mistake her younger sister removed some parts of it. Now she is totally confused about used module and other options:
from csv import writer with ............... ("cust.csv", "a", newline = "\n") as f: #line 1 dt = writer(...............) #line 2 while True: #line 3 sno= int (input ("Enter Serial No: ") ) cust_name = input ("Enter customer name: ") city = input ("Enter city: ") amt = int (input ("Enter amount: ") ) dt. ............... ( [sno, cust name, city, amt] ) #line 4 print ("Record has been added.") print ("Want to add more record?Type YES!!!") ch = input() #line 5 ch = ch.upper() if ch=="YES": print("*") else: breakAttempt any 4 out of the given questions:
(i) Name the method Sonia should use in Line 1.
(ii) Which object Sonia should use to connect the file in Line 2?
(iii) What is the use of Line 3?
(iv) Name the method she should use in Line 4.
(v) What is the use of Line 5?
To provide telemedicine facility in a hilly state, a computer network is to be set up to connect hospitals in 6 small villages (V1, V2, …, V6) to the base hospital (H) in the state capital. This is shown in the following diagram.
No village is more than 20 km away from the state capital. Imagine yourself as a computer consultant for this project and answer the following questions with justification:
(i) Out of the following, what kind of link should be provided to set up this network: Microwave Link, Radio Link, Wired Link?
(ii) Many a time doctors at the village hospital have to consult senior doctors at the base hospital. How should they contact them—through email, SMS, telephone or video conference?
(iii) Out of SMTP and POP3, which protocol is used to receive emails?
(iv) Expand the following terms:
(a) VoIP
(b) HTTPS
(v) Mention any two advantages of using Star topology.