Monday 11 June 2018
Sunday 10 June 2018
LIKE Operator
Irawen June 10, 2018 SQL No comments
The LIKE operator is used to search for a specified pattern in a column.
Syntax :-
SELECT * FROM table_name
WHERE column_name LIKE 'pattern';
Example :-
SELECT * FROM new_tab
WHERE name LIKE '%nu';
Wildcards:-
Wildcards are used to search for data within a table. These characters are used with the LIKE operator.
1. % - Zero or more characters
'Irawen%' - All starting with Irawen Ex:- Pirawen
'%shows' - All ending with shows Ex:- Pirawen
'%sh%' - All containing with sh. Ex:- Pirawen
2. - One single character
'show_ '- Starting with show then any character. Ex:- shows
'_rawen' - any character then eek. Ex:- irawen
'I_r_a' - I then any character, then r then any character, then a Ex:- -Irawen
Solve Example :-
Use LIKE operator :-
Syntax :-
SELECT * FROM table_name
WHERE column_name LIKE 'pattern';
Example :-
SELECT * FROM new_tab
WHERE name LIKE '%nu';
Wildcards:-
Wildcards are used to search for data within a table. These characters are used with the LIKE operator.
1. % - Zero or more characters
'Irawen%' - All starting with Irawen Ex:- Pirawen
'%shows' - All ending with shows Ex:- Pirawen
'%sh%' - All containing with sh. Ex:- Pirawen
2. - One single character
'show_ '- Starting with show then any character. Ex:- shows
'_rawen' - any character then eek. Ex:- irawen
'I_r_a' - I then any character, then r then any character, then a Ex:- -Irawen
Solve Example :-
Use LIKE operator :-
Thursday 7 June 2018
IN Operator
Irawen June 07, 2018 SQL No comments
OR Operator
Irawen June 07, 2018 SQL No comments
Wednesday 6 June 2018
AND Operator
Irawen June 06, 2018 SQL No comments
Friday 1 June 2018
SQL Statements and Rules
Irawen June 01, 2018 SQL No comments
SQL command or statement is a special kind of sentence that contains clauses and all end with a semicolon(;) just as a sequence ends with a period.
SQL statements are divided into four sub language:
(i) Data Definition Language (DDL):- It is used to define the structure of tables in the database.It contains the necessary statement to CREATE , RENAME , ALTER and DROP the tables.
(ii) Data Manipulation Language (DML):- It is a used to manipulate the data in the database.It contains statement to UPDATE , DELETE , INSERT and SELECT data that is stored in the database.
(iii)Data Control Language (DCL):- It is used to control data stored in the database.It contains statement give permission to access the data in the database.These statements are GRANT and REVOKE.
(iv) Transaction Control Language(TCL):- It is used to control the transaction in a database system. It contains statements like COMMIT , ROLLBACK and SAVEPOINT.
Rules for SQL Command/Statements
Rules for SQL commands are given below:
* SQL statements are not case sensitive.
* SQL statements can be executed on one or more tables.
* Keywords cannot be abbreviated.
* The statement can be typed in single line or multiple lines.
* Place a semicolon at the end of the SQL statements.
* A comma (,) is used to separate parameters without a clause.
* Characters and data constants or literals must be enclosed in single quotes(' ').
SQL statements are divided into four sub language:
(i) Data Definition Language (DDL):- It is used to define the structure of tables in the database.It contains the necessary statement to CREATE , RENAME , ALTER and DROP the tables.
(ii) Data Manipulation Language (DML):- It is a used to manipulate the data in the database.It contains statement to UPDATE , DELETE , INSERT and SELECT data that is stored in the database.
(iii)Data Control Language (DCL):- It is used to control data stored in the database.It contains statement give permission to access the data in the database.These statements are GRANT and REVOKE.
(iv) Transaction Control Language(TCL):- It is used to control the transaction in a database system. It contains statements like COMMIT , ROLLBACK and SAVEPOINT.
Rules for SQL Command/Statements
Rules for SQL commands are given below:
* SQL statements are not case sensitive.
* SQL statements can be executed on one or more tables.
* Keywords cannot be abbreviated.
* The statement can be typed in single line or multiple lines.
* Place a semicolon at the end of the SQL statements.
* A comma (,) is used to separate parameters without a clause.
* Characters and data constants or literals must be enclosed in single quotes(' ').
Saturday 21 April 2018
WHERE Clause and Equal Operator
Irawen April 21, 2018 SQL No comments
WHERE is used to search for a specific data.
Syntax :-
1. Specific data from all column
Syntax:-
SELECT * FROM table_name
WHERE column_name operator 'value';
Ex :-
SELECT * FROM new_tab WHERE name='Anu';
SELECT * FROM new_tab WHERE stu_id = 7;
2. Specific data from specific column
Syntax :-
SELECT column_name FROM table_name WHERE column_name operator 'value';
Ex:-
SELECT name FROM new_tab WHERE name = 'Anu';
SELECT name FROM new_tab WHERE stu_id = 5;
Example :-
Syntax :-
1. Specific data from all column
Syntax:-
SELECT * FROM table_name
WHERE column_name operator 'value';
Ex :-
SELECT * FROM new_tab WHERE name='Anu';
SELECT * FROM new_tab WHERE stu_id = 7;
2. Specific data from specific column
Syntax :-
SELECT column_name FROM table_name WHERE column_name operator 'value';
Ex:-
SELECT name FROM new_tab WHERE name = 'Anu';
SELECT name FROM new_tab WHERE stu_id = 5;
Note :- Value can be text or numeric. if it is text then we have to put single quotes.
Friday 20 April 2018
Select Statement
Irawen April 20, 2018 SQL No comments
The SELECT statement is used to select data from database and retrieve the information.
1. Select all columns from the table
Syntax :- SELECT * FROM table_name;
Ex :- SELECT * FROM my_tab;
2. Select Particular columns the table
Syntax :- SELECT column_name1, column_name2,..............FROM table_name;
Ex :- SELECT name, mobile FROM my_tab;
Example :-
1. Select all columns from the table
Syntax :- SELECT * FROM table_name;
Ex :- SELECT * FROM my_tab;
2. Select Particular columns the table
Syntax :- SELECT column_name1, column_name2,..............FROM table_name;
Ex :- SELECT name, mobile FROM my_tab;
Example :-
Thursday 19 April 2018
Insert into
Irawen April 19, 2018 SQL No comments
The INSERT INTO statement is used to insert new records/row/tuple in a table.
Syntax :-
INSERT INTO table_name (column1, column2, column3, column4,.....)
VALUES (value1, 'value2', 'value3', value4.....);
Ex :-
INSERT INTO my_tab (stu_id, name, address, mobile_no)
VALUES (05, 'Anu', 'Delhi', 982112);
Rules :-
Column and Value order should be same.
Any value that goes into a VARCHAR, CHAR, DATE, or TEXT column has single quotes around it. There are no need of quotes for numeric values (INT, DEC).
Example :-
Without specifying column name
Syntax :-
INSERT INTO table_name
VALUES(value1, 'value2', 'value3', value4);
Ex :-
INSERT INTO my_tab
VALUES (05, 'Anu', 'Delhi', 982112);
Rules :-
The values order should be same as column
We need to insert record for each column we can not leave any column.
Changing the order of column
Syntax :-
INSERT INTO table_name (column2, column1, column4, column3)
VALUES ('value2', value1, value4, 'value3');
Ex :-
INSERT INTO my_tab(name, stu_id, mobile_no, address)
VALUES ('Anu', 05, 982112, 'Delhi');
Insert Data only in Specified Columns
Syntax :-
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, 'value2', 'value3')
Ex :-
INSERT INTO my_tab(stu_id, name, address)
VALUES (05, 'Anu', 'Delhi');
Insert multiple records at one time
Syntax :-
INSERT INTO table_name (column1, column2, column3, column4)
VALUES (value1, 'value2', 'value3', value4);
(value1, 'value2', 'value3', value4);
Ex :-
INSERT INTO my_tab (stu_id, name, address, mobile_no)
VALUES (01, 'Anu', 'Delhi', 982112),
(02, 'Rohan', 'Mumbai', 561425);
Syntax :-
INSERT INTO table_name (column1, column2, column3, column4,.....)
VALUES (value1, 'value2', 'value3', value4.....);
Ex :-
INSERT INTO my_tab (stu_id, name, address, mobile_no)
VALUES (05, 'Anu', 'Delhi', 982112);
Rules :-
Column and Value order should be same.
Any value that goes into a VARCHAR, CHAR, DATE, or TEXT column has single quotes around it. There are no need of quotes for numeric values (INT, DEC).
Example :-
Without specifying column name
Syntax :-
INSERT INTO table_name
VALUES(value1, 'value2', 'value3', value4);
Ex :-
INSERT INTO my_tab
VALUES (05, 'Anu', 'Delhi', 982112);
Rules :-
The values order should be same as column
We need to insert record for each column we can not leave any column.
Changing the order of column
Syntax :-
INSERT INTO table_name (column2, column1, column4, column3)
VALUES ('value2', value1, value4, 'value3');
Ex :-
INSERT INTO my_tab(name, stu_id, mobile_no, address)
VALUES ('Anu', 05, 982112, 'Delhi');
Insert Data only in Specified Columns
Syntax :-
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, 'value2', 'value3')
Ex :-
INSERT INTO my_tab(stu_id, name, address)
VALUES (05, 'Anu', 'Delhi');
Insert multiple records at one time
Syntax :-
INSERT INTO table_name (column1, column2, column3, column4)
VALUES (value1, 'value2', 'value3', value4);
(value1, 'value2', 'value3', value4);
Ex :-
INSERT INTO my_tab (stu_id, name, address, mobile_no)
VALUES (01, 'Anu', 'Delhi', 982112),
(02, 'Rohan', 'Mumbai', 561425);
Wednesday 18 April 2018
Data Types
Irawen April 18, 2018 SQL No comments
INT or INTEGER - It holds whole number between -32,768 and 32,767 either it is negative or positive. It can not hold a decimal numbers. The maximum number of digits may be specified in parenthesis.
Syntax :-
column_name INT (size);
Ex :-
roll INT (5);
DEC or DECIMAL (size, p) - It holds fixed point numbers, size is the total number of digits and p is the number of digits after the decimal point. The decimal point and the negative sign '-' are not counted in size. If p is 0, values have no decimal point. The maximum number of size for decimal is 65 and for p 30. if p omitted the default is 0. if size is omitted, the default is 10.
Syntax :-
column_name DECIMAL(size, p);
Ex :-
price DECIMAL (4,2);
CHAR or CHARACTER - It holds a fixed length string (can contain letter, numbers, and special characters). The fixed size is specified in parenthesis. It can store up to 255haracters.
Syntax :-
column_name CHAR (20);
Ex :-
name CHAR (20);
VARCHAR - It holds a variable length string (can contains letters, numbers, and special character). The maximum size is specified parenthesis. It can store up to 255 characters.
Note : If we put a greater value than 255 it will be converted to a TEXT type.
Syntax :-
column_name VARCHAR (size);
Ex :-
name VARCHAR (50);
TEXT - It holds a string with a maximum length of 65,535 characters.
Syntax :-
column_name TEXT;
Ex :-
address TEXT;
DATE - It display Date values in yyyy-mm-dd format.
Syntax :-
column_name DATE;
Ex :-
age DATE;
DATATIME - It display DATETIME values in yyyy-mm-dd hh:mm:ss format.
Syntax :-
column_name DATETIME;
Ex :-
Date_of_join DATETIME;
TIMESTAMP - It also displayes date and time.
Syntax :-
column_name TIMESTAMP;
Ex :-
login_dt TIMESTAMP;
Syntax :-
column_name INT (size);
Ex :-
roll INT (5);
DEC or DECIMAL (size, p) - It holds fixed point numbers, size is the total number of digits and p is the number of digits after the decimal point. The decimal point and the negative sign '-' are not counted in size. If p is 0, values have no decimal point. The maximum number of size for decimal is 65 and for p 30. if p omitted the default is 0. if size is omitted, the default is 10.
Syntax :-
column_name DECIMAL(size, p);
Ex :-
price DECIMAL (4,2);
CHAR or CHARACTER - It holds a fixed length string (can contain letter, numbers, and special characters). The fixed size is specified in parenthesis. It can store up to 255haracters.
Syntax :-
column_name CHAR (20);
Ex :-
name CHAR (20);
VARCHAR - It holds a variable length string (can contains letters, numbers, and special character). The maximum size is specified parenthesis. It can store up to 255 characters.
Note : If we put a greater value than 255 it will be converted to a TEXT type.
Syntax :-
column_name VARCHAR (size);
Ex :-
name VARCHAR (50);
TEXT - It holds a string with a maximum length of 65,535 characters.
Syntax :-
column_name TEXT;
Ex :-
address TEXT;
DATE - It display Date values in yyyy-mm-dd format.
Syntax :-
column_name DATE;
Ex :-
age DATE;
DATATIME - It display DATETIME values in yyyy-mm-dd hh:mm:ss format.
Syntax :-
column_name DATETIME;
Ex :-
Date_of_join DATETIME;
TIMESTAMP - It also displayes date and time.
Syntax :-
column_name TIMESTAMP;
Ex :-
login_dt TIMESTAMP;
Tuesday 17 April 2018
DESC Command
Irawen April 17, 2018 SQL No comments
Create Table
Irawen April 17, 2018 SQL No comments
Syntax :-
CREATE TABLE table name
(
Column_name1 data-type (size),
Column_name2 data-type (size),
Column_name3 data-type (size)
);
Ex :-
CREATE TABLE my_tab
(
name varchar (30),
roll int (4),
address varchar (100)
);
Another way to create a table
Syntax :-
CREATE TABLE table name
(
Column_name1 data-type (size) [constraints],
Column_name2 data-type (size) [constraints],
Column_name3 data-type (size) [constraints]
);
Ex :-
CREATE TABLE my_tab
(
name varchar (30) NOT NULL,
roll int (4) PRIMARY KEY,
address varchar (100)
);
Guidelines for creation of Table
1. Table name should start with an alphabet.
2. In table name, blank spaces and single quotes are not allowed.
3. Reserve words of that RDBMS/DBMS cannot be used as table name.
4. Proper data type and size should be specified.
5. Unique column name should be specified.
CREATE TABLE table name
(
Column_name1 data-type (size),
Column_name2 data-type (size),
Column_name3 data-type (size)
);
Ex :-
CREATE TABLE my_tab
(
name varchar (30),
roll int (4),
address varchar (100)
);
Another way to create a table
Syntax :-
CREATE TABLE table name
(
Column_name1 data-type (size) [constraints],
Column_name2 data-type (size) [constraints],
Column_name3 data-type (size) [constraints]
);
Ex :-
CREATE TABLE my_tab
(
name varchar (30) NOT NULL,
roll int (4) PRIMARY KEY,
address varchar (100)
);
Guidelines for creation of Table
1. Table name should start with an alphabet.
2. In table name, blank spaces and single quotes are not allowed.
3. Reserve words of that RDBMS/DBMS cannot be used as table name.
4. Proper data type and size should be specified.
5. Unique column name should be specified.
Popular Posts
-
This textbook grew out of notes for the ECE143 Programming for Data Analysis class that the author has been teaching at University of Cali...
-
1. What is the output of following Python code? a = 'a' print(int(a, 16)) Solution and Explanation: Let's break down the expr...
-
Introduction to Modern Statistics is a re-imagining of a previous title, Introduction to Statistics with Randomization and Simulation. The...
-
What you'll learn Gain an immersive understanding of the practices and processes used by a junior or associate data analyst in their d...
-
Code: g = [1, 2, 3] h = [1, 2, 3] print(g is h) print(g == h) Solution and Explanation: In Python, the expressions g = [1, 2, 3] and h ...
-
Code: str_a = "hello" str_b = "hello" print(str_a is str_b) print(str_a == str_b) Solution and Explanation: In Pytho...
-
Code: dict_a = {"a": 1, "b": 2} dict_b = {"a": 1, "b": 2} print(dict_a is dict_b) print(dict_a == ...
-
Code: h = [5, 6, 7, 8] h.pop() h.pop(0) print(h) Solution and Explanation: Let's break down the given Python code and explain what ea...
-
Code: num = [7, 8, 9] *mid, last = num[:-1] print(mid, last) Solution and Explanation: let's break down the code: num = [7, 8, 9] *m...
-
import statsmodels.api as sm import numpy as np # Generate some sample data x = np.random.rand(100) y = 2 * x + np.random.randn(100) # Fit...
Categories
AI
(27)
Android
(24)
AngularJS
(1)
Assembly Language
(2)
aws
(17)
Azure
(7)
BI
(10)
book
(4)
Books
(118)
C
(77)
C#
(12)
C++
(82)
Course
(62)
Coursera
(180)
Cybersecurity
(22)
data management
(11)
Data Science
(95)
Data Strucures
(6)
Deep Learning
(9)
Django
(6)
Downloads
(3)
edx
(2)
Engineering
(14)
Excel
(13)
Factorial
(1)
Finance
(6)
flutter
(1)
FPL
(17)
Google
(19)
Hadoop
(3)
HTML&CSS
(46)
IBM
(25)
IoT
(1)
IS
(25)
Java
(92)
Leet Code
(4)
Machine Learning
(44)
Meta
(18)
MICHIGAN
(5)
microsoft
(4)
Pandas
(3)
PHP
(20)
Projects
(29)
Python
(753)
Python Coding Challenge
(232)
Questions
(2)
R
(70)
React
(6)
Scripting
(1)
security
(3)
Selenium Webdriver
(2)
Software
(17)
SQL
(40)
UX Research
(1)
web application
(8)