Database Administration

Scenario

You have been assigned the work on Datasette and perform the tasks like restoration of data and index creation to improve the query performance. You will create views to make queries easier to write.

The dataset used in this lab is billing.csv

Project

We’ll be working on the following aspects of Database Administration

Restore data
Indexing
View creation

Restore Data

Restore Table from CSV

View Total # of Rows in Table

Use the billing.csv and restore the CSV file into a table named billing. Write a query to display the total number of rows imported.
First you’ll see the script that created the table
Then the query to view the count of rows in the table

    CREATE TABLE [billing] (
    [customerid] integer,
    [category] text,
    [country] text,
    [industry] text,
    [month] text,
    [billedamount] integer
)

Create a view

Create a view named basicbilldetails with the columns customerid, month, billedamount

Create Index

Baseline query performance

Write a query to find out all the rows with a billing amount of 19929
Show the time for each row

SELECT 
        strftime('%Y-%m-%d %H:%M:%S', 'now'), *
FROM 
        billing
WHERE 
        billedamount = 19929

Create an index

Create an index that can make the query in the previous task faster.
Name the index as billingamount.

CREATE INDEX billingamount ON billing(billedamount)