Category Archives: Database

Database Interview Questions

Posted on March 9, 2017 | Leave a comment

http://jompesh.com/videos/1/1760/lesson-26-database-normalization

Theory site: http://www.studytonight.com/dbms/generalization-and-specialization.php
Another Resource: http://www.guru99.com/database-normalization.html

Leave a comment

Posted in Database, SQL

SQL Join – Interview Clear

Posted on March 5, 2017 | Leave a comment

http://www.sql-join.com/sql-join-types/

In below: The relationship between the two tables is specified by the customer_id key, which is the “primary key” in customers table and a “foreign key” in the orders table:

customer_id	first_name	last_name	email	address	city	state	zipcode
1	George	Washington	[email protected]	3200 Mt Vernon Hwy	Mount Vernon	VA	22121
2	John	Adams	[email protected]	1250 Hancock St	Quincy	MA	02169
3	Thomas	Jefferson	[email protected]	931 Thomas Jefferson Pkwy	Charlottesville	VA	22902
4	James	Madison	[email protected]	11350 Constitution Hwy	Orange	VA	22960
5	James	Monroe	[email protected]	2050 James Monroe Parkway	Charlottesville	VA	22902

order_id
order_date
amount
customer_id

1
07/04/1776
$234.56
1

2
03/14/1760
$78.50
3

3
05/23/1784
$124.00
2

4
09/03/1790
$65.50
3

order_id	order_date	amount	customer_id
1	07/04/1776	$234.56	1
2	03/14/1760	$78.50	3
3	05/23/1784	$124.00	2
4	09/03/1790	$65.50	3

Note that (1) not every customer in our customers table has placed an order and (2) there are a few orders for which no customer record exists in our customers table.

Inner Join Query:

select first_name, last_name, order_date, amount
from customer
inner join orders
on customer.customer_id=orders.customer_id

select first_name, last_name, order_date, amount

from customer

inner join orders

on customer.customer_id=orders.customer_id

Result:

first_name	last_name	order_date	amount
george	washington	07/04/1776	$234.56
john	adams	05/23/1784	$124.00
Thomas	Jefferson	03/14/1760	$78.50
Thomas	Jefferson	09/03/1790	$65.50

Leave a comment

Posted in Database, SQL

Tagged SQL Join

SQL

Posted on February 21, 2017 | Leave a comment

Good Learning Site for beginners:
Short:
http://www.dofactory.com/sql/left-outer-join
Elaborate:
https://www.w3schools.com/sql/sql_select.asp

Less Important But Okay:
https://www.tutorialspoint.com/sql/index.htm
Questions:

http://programabad.com/questions/5268/sql-normalization

http://www.studytonight.com/dbms/database-normalization.php

http://programabad.com/questions/3940/cgpa

Leave a comment

Posted in Database, SQL

Data Science MOOC (Will do someday)

Posted on February 18, 2017 | Leave a comment

http://www.kdnuggets.com/2015/09/top-20-data-science-moocs.html

https://lagunita.stanford.edu/courses/Home/Databases/Engineering/about

Leave a comment

Posted in Data Science, Database

Dive into some SQL queries

Posted on January 27, 2015 | Leave a comment

In my 7th semester Level-3 Term -1 of my BSc. Engineering course of Computer Science and Engineering in Daffodil International Univeristy(DIU) I am doing a course named Database Management System

Here our course teacher is using MS SQL Server 2008. I am sharing here what i have learnt from my class.

SQL stands for structured query language which help us to manage database easily

To create database in sql
Suppose a databse for university student

create database university;

1	create database university;

where we will create a table named student

create table university.dbo.student(sid int,sname varchar(20),age int);

1	create table university.dbo.student(sid int,sname varchar(20),age int);

after this we can show the empty table by this query

select * from university.dbo.student;

1	select * from university.dbo.student;

Now by this query we will insert data into the table

insert into university.dbo.student values(01,'Zaki Live',22);
insert into university.dbo.student values(02,'Zaki',23);
insert into university.dbo.student values(03,'Live',24);
insert into university.dbo.student values(04,'Mobin',21);
insert into university.dbo.student values(05,'Rahim',20);
insert into university.dbo.student values(06,'Rajdip',22);
select * from university.dbo.student;

insert into university.dbo.student values(01,'Zaki Live',22);

insert into university.dbo.student values(02,'Zaki',23);

insert into university.dbo.student values(03,'Live',24);

insert into university.dbo.student values(04,'Mobin',21);

insert into university.dbo.student values(05,'Rahim',20);

insert into university.dbo.student values(06,'Rajdip',22);

select * from university.dbo.student;

Don’t forget to use values.It is actually SQL syntax and don’t forget to give
select * from statement at the end of the query as it will help to display the updated table where star or asterisks sign * means all info of this table

if we want to show some limited data from all then here is one example

select * from university.dbo.student where sid=3;

1	select * from university.dbo.student where sid=3;

select sname,age from university.dbo.student where sid=3;

1	select sname,age from university.dbo.student where sid=3;

Now I am going to update some of my data in the table then

update university.dbo.student set sname='Rocky Live Dhaka !' where sid=3;
select*from university.dbo.student;

1 2	update university.dbo.student set sname='Rocky Live Dhaka !' where sid=3; select*from university.dbo.student;

Now I am going to delete some data from the table

delete from university.dbo.student where age=22 or age=23;
select * from university.dbo.student;

1 2	delete from university.dbo.student where age=22 or age=23; select * from university.dbo.student;

Then here see the change.Age name with 22 and 23 have been deleted for this sql command.

We have used here OR statement because AND is not working in MS SQL Server 2008.I don’t know why…May be I don’t know the actual things about and/or in MS SQL. I apologize for this..

If this tutorial helped you to understand the basics of the sql query.Then don’t forget to keep soem comment below.

Thanks for reading 🙂

Leave a comment

Posted in A ll Codes, Database, SQL

Tagged Database, sql