SQL

[Migration, Database Setting, DB Import/Export, Association, Model]

[SQL]

DB DUMP :- (Import/Expert)

Export one DB

$> mysql -u root -p database_name > dump_file {syntax}

$> mysqldump -u root -p gps_whatif > gps_whatif_db.sql

$> Password

Export one table

$> mysql -u root -p database_name table_name > dump_file {syntax}

$> mysqldump -u root -p gps_whatif products > gps_pro.sql

$> Password

Import products table into DB

$> mysql -u root -p database_name < dump_file {syntax}

$> mysql -u root -p gps_whatif < gps_pro.sql

$> Password

ROR Migration :-

What is Migration & Its Advantage ?

Migrations are a convenient way for you to alter your database in a structured and organized manner.

Rails Migration allows you to use Ruby to define changes to your database schema.

Making it possible to use a version control system to keep things synchronized with the actual code.

Adv:-

Teams of developers : If one person makes a schema change, the other developers just need to update, and run "rake migrate".

Production servers : Run "rake migrate" when you roll out a new release to bring the database up to date as well.

Multiple machines: If you develop on both a desktop and a laptop, or in more than one location, migrations can help you keep them all synchronized.

List out what can Rails Migration do?

• Create table
• Drop table
• Rename table
• Add column
• Rename column
• Change column
• Remove column and so on

What are the different process and syntax of migration ?

$> rails g migration filename {syntax}

$> rails g migration addNameToUser

$> rails g migration addNameToUser name:string

N:- only migration file will generate

$> rails g model modelname {syntax}

$> rails g model user name:string age:string

N:- modelname must be singular, by this command we will get both model & migration file generated. After that we can change migration file asper need create table, add column, rename etc

[Migration file change]

1/Creating table name

class CreateUsers < ActiveRecord::Migration

def change

create_table :users do |t|

t.string :firstname

t.timestamps null: true

end

end

end

2/ Add a Column to Table

class AddColumnToOrganisation < ActiveRecord::Migration

def change

add_column :table_name, :column_name, :data_type

end

end

3/ Rename column name of a Table

rename_column :table_name, :old_name, :new_name

4/ Change data type of a column

change_column :table_name, :column_name, :new_datatype

5/ Drop a column of a table

remove_column :table_name, :column_name

6/ Drop a table

drop_table :table_name

7/ add new features to column like Uniqueness

add_index :users, :fname, unique: true

Database Setting :-

project/config/database.yml

default: &default

adapter: mysql2

pool: 5

timeout: 5000

development:

<<: *default

database: sample_test

username: root

password: orbio123

production:

<<: *default

database: sample_test

username: root

password: orbio123

N:- In database.yml we need to mension adapter: mysql2 either in default: or development:

and we need to add gem in Gemfile ---- gem 'mysql2'

and bundle install

Multiple database in single application

project/config/database.yml

default: &default

adapter: mysql2

pool: 5

timeout: 5000

development:

<<: *default

database: sample_test

username: root

password: orbio123

development_sec:

adapter: mysql2

database: sample

username: root

password: orbio123

host: localhost

production_sec:

<<: *default

database: sample

username: root

password: orbio123

N :- development: is default env so we do not need any connection explicitly .

But for production_sec: env we need to connection explicitly as below

project/app/model/group.rb (groups table in sample_test database)

class Group < ActiveRecord::Base

establish_connection "#{Rails.env}_sec"

end

[Migration for Second Database]

$> rails g migration subgroup

project/db/migrate/migrationfile.rb

class CreateConections < ActiveRecord::Migration

def connection

ActiveRecord::Base.establish_connection("#{Rails.env}_sec").connection

end

def change

create_table :conections do |t|

t.timestamps null: false

end

end

end

N :- connection action can not be any other action.

SQL

What is SQL?

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database.

SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language.

Also, they are using different dialects, such as:

• MS SQL Server using T-SQL,
• Oracle using PL/SQL,
• MS Access version of SQL is called JET SQL (native format), etc

Types:

Data Definition Language (DDL) Statements

Data definition statement are use to define the database structure or table.

Statement	Description
CREATE	Create new database/table.
ALTER	Modifies the structure of database/table.
DROP	Deletes a database/table.
TRUNCATE	Remove all table records including allocated table spaces.
RENAME	Rename the database/table.

Data Manipulation Language (DML) Statements

Data manipulation statement are use for managing data within table object.

Statement	Description
SELECT	Retrieve data from the table.
INSERT	Insert data into a table.
UPDATE	Updates existing data with new data within a table.
DELETE	Deletes the records rows from the table.
MERGE	MERGE (also called UPSERT) statements to INSERT new records or UPDATE existing records depending on condition matches or not.
LOCK TABLE	LOCK TABLE statement to lock one or more tables in a specified mode. Table access denied to a other users for the duration of your table operation.
CALL EXPLAIN PLAN	Statements are supported in PL/SQL only for executed dynamically. CALL a PL/SQL program or EXPLAIN PATH access the data path.

Data Control Language (DCL) Statements

Data control statement are use to give privileges to access limited data.

Statement	Description
GRANT	Gives privileges to user for accessing database data.
REVOKE	Take back for given privileges.
ANALYZE	ANALYZE statement to collect statistics information about index, cluster, table.
AUDIT	To track the occurrence of a specific SQL statement or all SQL statements during the user sessions.
COMMENT	Write comment to the data table.

Transaction Control Statement (TCS)

Transaction control statement are use to apply the changes permanently save into database.

Statement	Description
COMMIT	Permanent work save into database.
ROLLBACK	Restore database to original form since the last COMMIT.
SAVEPOINT	Create SAVEPOINT for later use ROLLBACK the new changes.
SET TRANSACTION	SET TRANSACTION command set the transaction properties such as read-write/read only access.

PL/SQL Transaction Commit, Rollback, Savepoint, Autocommit, Set Transaction read more.

Session Control Statements (SCS)

Session control statement are manage properties dynamically of a user session.

Statement	Description
ALTER SESSION	ALTER SESSION statement to modify conditions or parameters that are affect to your database connection.
SET ROLE	SET ROLE statement to enable or disable the roles that are currently enabled for the session.

Why SQL?

• Allows users to access data in relational database management systems.
• Allows users to describe the data.
• Allows users to define the data in database and manipulate that data.
• Allows to embed within other languages using SQL modules, libraries & pre-compilers.
• Allows users to create and drop databases and tables.
• Allows users to create view, stored procedure, functions in a database.
• Allows users to set permissions on tables, procedures, and views

Differences between SQL and PLSQL?

SQL

PLSQL

SQL is a data oriented language for selecting and manipulating sets of data SQL may be the source of data for our screens, web pages and reports. SQL is executed one statement at a time. SQL tells the database what to do (declarative), not how to do it. In contrast SQL is used to code queries, DML and DDL statements. we cannot embed PL/SQL within a SQL statement.

PL/SQL is a procedural language to create applications. PL/SQL can be the application language just like Java or PHP can. PL/SQL might be the language we use to build, format and display those screens, web pages and reports. PL/SQL is executed as a block of code. PL/SQL tell the database how to do things (procedural). PL/SQL is used to code program blocks, triggers, functions, procedures and packages. We can embed SQL in a PL/SQL program,

Differences between SQL and PLSQL?

• SQL is a data oriented language for selecting and manipulating sets of data. PL/SQL is a procedural language to create applications.
• PL/SQL can be the application language just like Java or PHP can. PL/SQL might be the language we use to build, format and display those screens, web pages and reports.SQL may be the source of data for our screens, web pages and reports.
• SQL is executed one statement at a time. PL/SQL is executed as a block of code.
• SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tell the database how to do things (procedural).
• SQL is used to code queries, DML and DDL statements. PL/SQL is used to code program blocks, triggers, functions, procedures and packages.
• We can embed SQL in a PL/SQL program, but we cannot embed PL/SQL within a SQL statement.

What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

What is table ?

The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.

Remember, a table is the most common and simplest form of data storage in a relational database.

What is field?

Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY.

A field is a column in a table that is designed to maintain specific information about every record in the table.

What is record or row?

A record, also called a row of data, is each individual entry that exists in a table. For example, there are 7 records in the above CUSTOMERS table.

A record is a horizontal entity in a table.

What is column?

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

What is NULL value?

A NULL value in a table is a value in a field that appears to be blank which means A field with a NULL value is a field with no value.

It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

What does UNION do? What is the difference between UNION and UNION ALL?

UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALLwill include duplicate records.

It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.

SQL QUERY:

Create/Drop DB

create database sample;

drop database sample;

Create/Drop Table;

use sample;

create table table_name;

drop table table_name;

Insert :

insert into table_name(col1, col2,col3 ...)

values ('col1_val','col2_val','col3_val', ...),('col1_val','col2_val','col3_val', ...)

Select:

select * from table_name limit 5 offset 5;

select col from table_name;

Limit 5 : - it will display the result max 5 row.

Offset 5 : - it will skip first 5 row.

Where :-

select * from table_name where id = 5; (conditions)

#Multiple Table

select * from students s, tutorials t where s.id = t.id;

Update:- (SET)

update students set col_name = 'new_value' where conditions;

Delete:

delete from table_name where conditions;

Like :-(% , _)

name='Bishnu'

select * from table_name where col_name like “%Bish%”

select * from table_name where col_name like “Bishn_”

Order By:

select * from table_name order by col_name ASC/DESC

Group By:

select * from table_name group by col_name;

# group by must precede the ORDER BY clause if one is used. & order by must precede the LIMIT.

select * from tutorials order by id desc group by dept;(wrong)

select * from tutorials group by dept order by id desc; (Right)

select * from tutorials group by dept order by id desc limit 5; (Right)

select * from tutorials group by dept limit 5 order by id desc; (wrong)

Distinct :

select distinct name from table_name; #remove only name duplication

select distinct name, age from table_name;#remove if name& age both will be same .

Joins:

Inner Join:

select * from table1_name ref1 inner join table2_name ref2 on ref1.id = ref2.id;

Left join:

select * from table1_name ref1 left join table2_name s on ref1.id = ref2.id;

Right Join:

select * from table1_name ref1 right join table2_name ref2 on ref1.id = ref2.id;

Full Outer Join: (MySql#Union PostgreSql#full join)

select t.name,s.name from students s left join tutorials t on s.id = t.id union all select t.name,s.name from students s right join tutorials t on s.id = t.id; {MySQL}

select * from table1_name ref1 full join table2_name ref2 on ref1.id = ref2.id;{PostgreSql}