CSE3

The SQL chapter

if a solved skip

any solutions got the wrong way to do, the right way to do it from the point of view of the client's needs and views but this will be the back-end that the client will not see or understand but it's the main backbone for the solution.

~system architecture

all systems from a system creation point of view are forms and reports, taking in mind the database, the operating system that is running, the network that the system will be in, and the layout.

So with that notion let's first try to name and Siem-understand the forms that we need to create.

Forms --------

Now the forms are ready let's try to create some reports to display for the user (: logic being that both of us can have a common view of what will happen)

reports ------

estimated normal time to know all of these 30 hours

if you can answer those questions easily then skip this chapter

->after a meeting or a handout from the analytic team, create a database that at least can stand for 5 years. (hopeful thinking)

-> a user wants to add data inside the system

-> a user wants to edit data he previously added.

-> get all the data that is needed to create a report.

-> get some of the data that the manager requested.

if you cant let's start with this scenario

Payment (accounting related)

related concepts (payment, economics, exchange, how to market works, price of maintenance, drawback, guidance)

let's create a Database

This example is a must, intending to show how it's not hard to do a solution for anyone if the requirements are well understood.

-table for the payments

-table for the users

-table for the current exchange rate

-table for the drawback and fails

table name

field name - datatype

------------------------------

payments

id - number

amount - number

date - date

------------------------------

*since we can't use the date as a name as well it is better to change it to date + what this date is for.

------------------------------

payments

datepayments - date

------------------------------

a table to store the user data

------------------------------

users

id - number

username - char

------------------------------

now to add a relationship between the payments and who did the payments (users)

------------------------------

payments

username - char

------------------------------

concepts need to be kept in mind:

-all the basic data types should be well known, numbers, fraction numbers, dates.

-how to work with the database itself,

how to remove data, how to add data and how to edit that data inside the database(DML)

before all of this, how to create tables, how to remove them, and how to edit their structure if needed (DDL)

for the sake of not writing much and keeping it simple, all tables that will be created should have TableID which will be (primary key and not null), auto-increment

->CREATE TABLE payments (

TableID INT NOT NULL AUTO_INCREMENT,

amount FLOAT(255),

DATEPAYMENTS date(255)

);

->ALTER TABLE payments ADD username varchar(255);

a quick note if need to remove a COLUMN in place of add just DROP COLUMN, and to remove a table DROP TABLE payments ;

->INSERT INTO payments (TableID , amount , DATEPAYMENTS , username )

VALUES (NULL, '200', '1993-12-23', 'serotonin');

->update payments set amount = '50' where username = 'serotonin'

->delete from payments where username = 'serotonin'

let us take another quick note,

• to know if your current SQL works or not, read it as if it's simply broken English.

• when updating or deleting its -a must - to use where, so you don't remove or edit the whole table.

from the retrieve here are some examples that might come in handy

selecting the last record (say the last entry is needed )

->select TableID , amount ,DATEPAYMENTS ,username from payments order by TableID desc limit 1

to get the minimum payment (say the least amount is needed)

->select min(amount) from payments

to get the max payment for each username.

->select max(amount) from payments group by username

to get the sum payment for each username

->select sum (amount) from payments group by username

to get the name of the usernames without duplication

->select distinct(username ) from payments

to get all the payments exactly from 2 days

->select amount from payment where DATEPAYMENTS in ('1993-12-22','1993-12-23')

to get all the payments from date to date

->select amount from payment where DATEPAYMENTS between '1993-12-22' and '1993-12-23'

to get data from multiple tables that are related (using join)

->select username, amount from payments, user where

username.id = payments.users

• now for some literature

Why database? so we store data and long-term comparison of data.

How is security? database access should have a username and password and the data itself should have accountable factors and time built in.

• Data types

varchar2 (char,string)

number (int, float)

date (timestamp)

• operations that can be used: arithmetic, comparison, logical operators, and/or/not

• things to worry about

-poor support for concurrent data access

-database normalization

Note:

At early levels of dealing with a database, it's better to use a third-party program to help with creating, inserting data code, updating data code, and then removing the data code.

in short Database normalization,

We care for the shape of the database, non-duplicate fields, and the relationship between fields.

next to read about

->joins, DML(insert , update , delete),DDL(alter , drop,create)

------------------------------------------------------------------------