Explore BrainMass
Share

How to Create a Database using SQLite

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

Provide SQL statements to create the database and populate it with sample data at least 4 rows per table
modify the database by adding an additional column "Deleted_Date" to those tables that represent entities that could contain data which can be deleted and justify your rationale in a short paragraph.

Explain what the tradeoffs are in using a marker-column for deletion as opposed to actually deleting a row from the table.

© BrainMass Inc. brainmass.com October 25, 2018, 9:02 am ad1c9bdddf
https://brainmass.com/computer-science/databases/561860

Solution Preview

Hello,

My name is Jim. I hope I will answer any questions you have.

The nature of this assignment is to learn the basic syntax of SQL using SQLlite.

The first thing you'll need to do is to decide what data types you'll need in your table (or tables). The entities you've listed, "date time height weight temp. Pulse Resp BP systolic BP diastolic provider id", seem to be almost all numeric values.

For the date and time, SQL has a specific data type for dates and times, called the "date" type. If you use that it can help you avoid problems like having a date like "September 31, 2013" entered. (September only has 30 days in it.) It also allows you to do things like date arithmetic, so that you can easily figure out how many days there are between two dates, like from September 18, 2012 to December 25, 2012. It also allows you to retrieve the date from the column in a number of different formats depending on what you want (like as "12/25/2012", or as 25-December-2012"). There's also typically a function that will allow you to retrieve a date/time stamp as an integer containing the number of seconds that have elapsed from January 1, 1970 until the date/time stamp in the specific row and column.

The numeric fields, weight, and blood pressure values are all typically integers (whole numbers without a fractional part or a decimal point and numbers to the right of it). So the normal "int" data type would be appropriate. In SQL they also distinguish between the various sizes of integers you might be storing. Obviously blood pressure and weight don't typically get very high, certainly not high enough to warrant the number having, say, 10 digits. So the smaller int data types would be more appropriate (like int(2) and int(4).

The temperature is also a numeric field, but typically is expressed as a real number or floating point number, as in "98.6" which is the normal body temperature in degrees Fahrenheit. Since you will want to store fractions the data type for the temperature column will have to be different from the weight and blood pressure readings. The specific name is usually something like "double" or "float". That's something you can look up in the manual.

You'll have to decide for yourself what type of field is best to hold the height in. You could store it as a simple character string, for example. Then the data stored in the database would look something like this: 5' 4", though storing it like this: 5ft 4in might be easier for you to manipulate with the queries, because a character string value is typically denoted with opening and closing parenthesis. Or, you could use your own convention and store it like this: 5,4, with the number to the left of the comma being feet and the number to the right being inches. Please keep in mind that if the field is entered into the database, or that column is qualified in the where clause of a select statement, you will have to enclose the height in character strings. That's what I was trying to make easier with the comma and the "ft" and "in". If you wanted to find all the people that are 5 feet 6 inches, you'd have to have something like this at ...

Solution Summary

This (approximately) 2,200 word document will help the beginner create database tables using SQLite. It includes a discussion of choosing specific data types, and some of the various attributes. The date data type is covered with some detail. The specific syntax of creating tables is covered, as are the data manipulation statements (select, insert, and delete).

$2.19
See Also This Related BrainMass Solution

SQLite - Grouping Records and Aggregate Functions

Please see attached file. I have to use software SQLite, database attached along with questions.

9.2 Grouping Records & Aggregate Functions

Aggregate functions are used to calculate summary information.

Let's assume that every product ordered from the company is supplied in a case of 12 items. An order for 50 units, for instance, means 50 cases of 12 items.

You can create a temporary value that holds the total number of items ordered by entering a statement as shown below. In this example, TotalItems is a temporary value created when the query runs.

SELECT *,
Quantity * 12 AS TotalItems
FROM CustomerData;

1. Try this out so you can see how the results are shown.

Let's assume that A1 costs £0.22 per unit. Now try the following:

2. Create a query that uses a temporary value called OrderValue to hold the total value of each order for A1. Hint: you don't need to calculate TotalItems

Some of the aggregate functions available to you will probably be familiar. These include MAX, MIN, SUM, AVG and COUNT. To count how many records are in the database, for instance, you might enter:

SELECT COUNT(*) FROM CustomerData

3. Try these queries:

a) What is the largest quantity of items ordered?
b) What is the smallest quantity of items ordered?
c) What is the average order size?
d) Can you calculate all of these figures at one time?
e) Can you calculate the biggest/smallest orders for product "C1"?
f) What is the total number of orders for product "A1"?

You can use GROUP BY to group results together. To calculate orders per region, for instance, you might group by postal code area:

SELECT *,
SUM(Quantity)
FROM CustomerData
GROUP BY [Post Code Area];

4. Calculate the total number of orders per product code.

View Full Posting Details