Several steps are necessary to be followed for this assignment:
- Downloading Steps:
- go to official site of sqlite i.e.
- click on downloads
- click on
- it will download a zip file
- extract all and run the sqlite3
- it will open a command line window
- Creating the database
- To create the database use command ‘.open database_name.db’.For example I want to create database with ‘my_student_id.db’.So I will write the command as ‘.open myid.db’.
- As I have to import the text file data to my database file, I have to write the read command that is ‘.read a5.txt’.
- After this command all data will be imported in ‘my_id.db’
- Formatting the output(Optional)
.mode: The result of a sqlite query can be shown in eight different formats that are
The .mode command is used to use one of these formats. By default the output is displayed in the list format. To change we have to write the ‘.mode ’ command with an option one of these. As I want to format my output in the form of rows and columns that’s why I am using ‘.mode column’.
.header: ‘.header ‘command is used with two options on and off.As I want to to display header in my output I am using the command ‘.headers on’
- Displaying all tables
To show all tables i have used ‘.tables’ command.
Part A – DML
1.Locate the record in the vendor table that does not have a value for the attribute V_STATE
In first query it is asked to display the records of vendor table for which v_state column does have any value that mean v_state contains null. For that we have to use where clause to put the condition in for retrieving desired information.
- Find the customers whose balance is greater than $300 but less than $400.
For this query we have to find the records in the customer table where balance is greater than 300 and less than 400.To achieve the result we can use and operator with two conditions or we can use between clause.
3.Show the names of the customers for whom we have invoices.
Here we have to find the name of the customers that have invoices. Fot that I have used join on two tables that are customer and invoice. It will display names of those customer only where customer code of customer table and invoice table are equal.
- What is the max balance?
To display the maximum customer balance I have used an aggregate function max(). Max(cus_balance) will displays the maximum value of cus_balance column in customer table.
- Show the CUS_CODE of the customers for whom we DO NOT have invoices.
We can retrieve this information by using NOT IN.As we have to fetch the cus_code of those customer where customer code does not exist in invoice table.As a parameter to IN we have to pass the subquery that find the cus_code where invoice .cus_code=customer.cuscode
- Show the names of the customers for whom we DO NOT have invoices.
This query is same as query five the only difference is here we have to display the first name and last name of the customer.
- Find out how many times a customer generated an invoice – make sure the counted column heading reads “Invoices_generated”.
To retrieve this information we have to use inner join on two tables customer and invoice.and to count the generated invoices the aggregate function count() is used. And to give the column heading we can use alias by using ‘as’ keyword. the alias for new column is “Invoices_generated”.
- List the names of the customers and how many times a customer generated an invoice – make sure the counted column heading reads “N_Of_Invoices”.
This query is same as the previous one. The difference here is in this we have to project the columns first name and last name of customer table. The result of this query will display how may times a customer generated invoice with name to the corresponding customer. Here the alias for new column is “N_Of_Invoices”.
Part B – DDL
1.Add a new row/record to the `customer` table to include your actual student_ID in the CUS_CODE, your last_name as the CUS_LNAME` and yourfirstname as CUS_FNAME. Add any other details of your choice in all the other fields.
This query is used to add one reacord in the customer table where I have insert my student id as cus_code and my firstname as customer firstname and my lastname as customer last name and all my details for other columns
- Add a new attribute (field) credit_history VARCHAR(6) to the customer table.
To implement this query we have to change the structure of the table customer by using alter command.
In alter command we have to use add column option with column name i.e credit_history and its datatype i.e VARCHAR and the size i.e 6.
- Update credit_history for customer ‘10011’to ‘Bad’
For this we have to use the update query to update one record in customer table.we have to set the value ‘Bad ’ where the cus_code is ‘10011’.
4. Delete the employee Leighla Genkazi from the EMP table.
In this query we have to delete one record from emp table where the emp_fname=’ Leighla’ and emp_lname=’ Genkazi’
- Add a new attribute called ‘email_address’ to ‘emp’ table with data type and length ‘VARCHAR(20).
To alter the structure of the table emp we have to use alter table query with add column option where the column name is ‘email_address’ and its datatype is VARCHAR and the length is 20.