Understanding Databases using SQL - Basic

•MySQL – OpenSource Software

•Oracle 10g – Proprietary Software

•In MySQL , root is an admin user

•In Oracle 10g , system is an admin user

•The g in Oracle 10g is grid, meaning many group including a database management system (DBMS) and an application server.

MySQL - open source database - structured query language

mysql>show databases;  There can be many databases which contain many tables inside it

mysql>create database db1;

mysql>use db1;

mysql> create table student(rollno int(3),name varchar(10),birth_dt date);

mysql> desc student;

datatypes are integer , varchar and date -->

integer allows to store numbers,

varchar allows to store alphanumeric values

date allows to store date in yyyy-mm-dd format

mysql>insert into student values(1,'A','2000-03-23');

How in case of MySQL, root is the admin in the same manner, in Oracle 10g,we have system user.

We can create user and then will work in the same.

Rest of the commands we will do in Oracle 10g

Oracle 10g - SQL - g for grid.

Here, there is no integer data type , instead of it there is a number datatype.

SQL>create table student(rollno number(3),name varchar(10),birth_dt date); date is stored in dd-mon-yyyy format. when we give varchar datatype, varchar2 is used automatically.

SQL>insert into student values(1,'A','2000-03-23');

Till now, we saw create database in mysql, create table in mysql and oracle 10g and inserting records in both. Even we spoke about the insert statement and select stmt.

I told you all in short about

DQL

DML and DDL queries

DQL= Select

DDL= create table

DML=insert

There are many more..

later told you about where clause. in Insert, into clause too.

Today we will understand how to give update statement.

update with where clause and without where clause.

You can check the same in SQL statements provided as a help.

we are learning various statements which come under DML

We studied -

1. insert

2. update

3. delete

DQL - select

DDL- create

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

We will see delete statement.

it allows to delete records, with and without where clause

Logical operators with where clause

AND,OR,NOT

Entity relationship model

Mapping Cardinalities

1:1

1:m

m:n Relationships

refer ppt and video

Constraints -

Constraints are the conditions which are implemented on tables. for ex to enter unique values or not null values or check constraint etc.

The use of Primary key/unqiue constraints is to allow to add records which are unique i.e. Primary keys for the field or which are not null(empty) or the Foreign keys.

This can be done using DDL queries as below -

SQL>create table student(rollno number(3),name varchar(10), primary key(rollno));

Here we are declaring our primary key in student table for rollno.

It would allow to add all unique values and which are not null(empty) when we will execute the insert query.

SQL>insert into student values(1,'m');

if we try to insert the same rollno again then it would show an error saying "unique constraint violated"

The meaning is the primary key is implemented on table. It would accept all unqiues values for empno.

As values for ename, or gender or salary or job can be repeated. But empno can't be.

in the same manner other constraints like

Foreign Key, Not Null, unique and Check constraint.

The use of add constraint with constraint name is used when there are chances that more than one column would have same cosntraint like Foreign Key, Unique, Not Null or Check constaint.

The constraint is allowed to be given only in DDL queries that is while creating or making alterations in table.

Primary key -

alter table emp1 add primary key(empno);

or

alter table emp1 add constraint pkey primary key(rollno);

Foreign Key Constraint -

When  the primary key is there, then only the foreign key can be there. Indirectly, FK is dependent on PK.

To join foreign key, we will have to create new table say jobs.

I am assuming there is a relationship between emp1 and jobs in such a way that 1 emp can do many jobs(reminding 1:m relationship, see the last video)

create table jobs(jobid number(3) primary key, job_title varchar(15), empno number(3), foreign key(empno) references emp1(empno));

The foreign key/primary key can be added using create table or alter table.

The use of add constraint with constraint name is used when there are chances that more than one column would have same cosntraint like Foreign Key, Unique, Not Null or Check constaint.

The constraint is allowed to be given only in DDL queries that is while creating or making alterations in table.

Primary key -

alter table emp1 add primary key(empno);

or

alter table emp1 add constraint pkey primary key(rollno);

Foreign Key Constraint -

When  the primary key is there, then only the foreign key can be there. Indirectly, FK is dependent on PK.

To join foreign key, we will have to create new table say jobs.

I am assuming there is a relationship between emp1 and jobs in such a way that 1 emp can do many jobs(reminding 1:m relationship, see the last video)

create table jobs(jobid number(3) primary key, job_title varchar(15), empno number(3), foreign key(empno) references emp1(empno));

The foreign key/primary key can be added using create table or alter table.

Not Null Constraint  - no null records are allowed to enter, for ex. name has to be there, contact_number is required or email_id is required then it can have Not Null constraint.

alter table emp2 add not null(ename);

Unique Key Constraint-

alter table emp2 add constraint ukey unique(email_id);

Check Constraint -

alter table student add constraint chk check(mks>50); assuming mks column is there.

Note - all the constraints have to be added in DDL but can be seen them working in DML , i.e. while adding records.

After learning all DDL/DML statements, Entity Relationship model and constraints there is a need to understand how to create m:n relationship

In many to many relationship , the primary keys have to be created on both tables which are connected to each other and their foreign keys go to the third table attached to the realtionship,a dependent table.

in last session we saw two tables , emp and jobs which had got 1:m relationship.

But if we say, emp and company might have m:1 relationship(many emp working in one company). But in other way round, it can be m:n relatioship in such a way that, many employees working in one company, and 1 employee working in many companies(we can say that on various projects he is working on various companies at various locations)

So locations can be third table, locations dependent on emp and company entities

So we will have to create all tables one by one ..

1. Create emp(empno, ename, salary)  --PK empno

2. Create company(co_id, co_name,tel_no) --PK co_id

3. Create locations(empno,co_id, locn_nm) --Foreign Key -empno,co_id from two connected table/entity

4. If we keep FK as it is, it can take repeated values and/or empty values(NULL)

So create joint FK(together) , we can create upto 16 columns like this PK/FK or Unique key in joint manner

5. Create PK for the FK so that the values wont be repeated/NULL

SQL> --in between like

Already in DQL we learnt how to use AND, OR, NOT(logical) operators along with relational op.

We will see the operators/clauses like in, between and like.

in- select * from emp where sal in(20000,30000); to find the data of all emp where sal as either 20k or 30k

IN works like OR(logical operator)

Between with and - select * from emp where sal between 20000 and 30000; This is known as a range op and works on values which are inclusive

Between works like AND(logical op)

Like is used for searching the text with the wildcard symbols like _ and %