SQL – PARVATISHIV

BASICS OF DATABASE

Java was the best language for database connectivity five years ago. There were many reasons why it lost the title. The competitors did not want to work on a third-party technology. For example, Microsoft tried to push Python and SQL on Microsoft Visual Studio. Many IDE came up which could support Python for mobile app development. Moreover, there were many reasons as to why NOSQL stayed behind. It had lesser features. In conclusion, SQL is still the most popular database tool for big data and AI based data mining. People lost interest in manual coding because of growing popularity of AI based SQL coding. The syntax of MYSQL and SQL server is 95 percent same. The features of capabilities of them are also too similar.

ORACLE SQL NOTES

ALTER table classics ADD DOB DATE;

INSERT INTO classics VALUES (‘kunal’, ‘database’, ‘Technology’, ‘2021’, 800, 9417054897,’2005-07-04′);

mysql> INSERT INTO classics VALUES (‘gagneja’, ‘cryptography’, ‘Technology’, ‘2021’, 800, 9417054897,’2004-06-02′);
ERROR 1062 (23000): Duplicate entry ‘9417054897’ for key ‘PRIMARY’

SELECT author, year, CURDATE(), TIMESTAMPDIFF (year, title, CURDATE()) AS age from classics; warning warning

SHOW warnings;

SELECT ‘2018-10-31’ + INTERVAL 1 DAY ;
SELECT ‘2018-10-31’ + INTERVAL 10 MONTH ;
SELECT ‘2018-10-31’ + INTERVAL 10 DAY ;

SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

mysql> SELECT ‘2018-10-31’ + INTERVAL 1 DAY ;
Empty set (0.00 sec)

SELECT * FROM classics WHERE author LIKE ‘k%’;
SELECT * FROM classics WHERE author LIKE ‘%en’;

SELECT * FROM classics WHERE author LIKE ‘_‘;
+——–+———-+————+——+——-+————+————+
| author | title | category | year | pages | isbn | DOB |
+——–+———-+————+——+——-+————+————+
| kunal | database | Technology | 2021 | 800 | 9417054897 | 2005-07-04 |
+——–+———-+————+——+——-+————+————+

SELECT * FROM classics WHERE title REGEXP ‘cryptography$’;\
+————–+————–+————+——+——-+———–+————+
| author | title | category | year | pages | isbn | DOB |
+————–+————–+————+——+——-+———–+————+
| Engineer k g | cryptography | Technology | 2021 | 800 | 919454897 | 2006-02-05 |
| gagneja | cryptography | Technology | 2021 | 800 | 941705497 | 2004-06-02 |
+————–+————–+————+——+——-+———–+————+
2 rows in set (0.00 sec)

mysql> SELECT * FROM classics WHERE author REGEXP ‘kl$’;\
Empty set (0.00 sec)

mysql> SELECT * FROM classics WHERE author REGEXP ‘kunal$’;\
+——–+———-+————+——+——-+————+————+
| author | title | category | year | pages | isbn | DOB |
+——–+———-+————+——+——-+————+————+
| kunal | database | Technology | 2021 | 800 | 9417054897 | 2005-07-04 |
+——–+———-+————+——+——-+————+————+
SELECT * FROM classics WHERE author REGEXP ‘w’;
SELECT * FROM classics WHERE author REGEXP ‘^…..$’;
SELECT * FROM classics WHERE author REGEXP ‘^.{5}$’;
SELECT COUNT() FROM classics; SELECT title, COUNT() FROM classics GROUP BY author;
SELECT title, COUNT() FROM classics GROUP BY title; SELECT author, title, COUNT() FROM classics GROUP BY category, year;
SELECT author, title, COUNT(*) FROM classics WHERE Year <1900 OR ISBN = 9597920640 GROUP BY year; SELECT author, title, COUNT(*) FROM classics WHERE Year >1900 OR ISBN = 9597920640 GROUP BY year;
SELECT now();
SELECT
-> NOW()\g
SHOW FULL COLUMNS FROM limbs LIKE ‘thing’\G
SELECT * from limbs\G
DO IT DO IT DO IT DO IT DO IT DO IT
DROP TABLE IF EXISTS limbs;
CREATE TABLE limbs
(
thing VARCHAR(20), # what the thing is
legs INT, # number of legs it has
arms INT # number of arms it has
);
INSERT INTO limbs (thing,legs,arms) VALUES(‘human’,2,2);
INSERT INTO limbs (thing,legs,arms) VALUES(‘insect’,6,0);
INSERT INTO limbs (thing,legs,arms) VALUES(‘squid’,0,10);
INSERT INTO limbs (thing,legs,arms) VALUES(‘fish’,0,0);
INSERT INTO limbs (thing,legs,arms) VALUES(‘centipede’,100,0);
INSERT INTO limbs (thing,legs,arms) VALUES(‘table’,4,0);
INSERT INTO limbs (thing,legs,arms) VALUES(‘armchair’,4,2);
INSERT INTO limbs (thing,legs,arms) VALUES(‘phonograph’,0,1);
INSERT INTO limbs (thing,legs,arms) VALUES(‘tripod’,3,0);
INSERT INTO limbs (thing,legs,arms) VALUES(‘Peg Leg Pete’,1,2);
INSERT INTO limbs (thing,legs,arms) VALUES(‘space alien’,NULL,NULL);
SELECT @max_limbs := MAX(arms+legs ) FROM limbs;
SELECT @min_limbs := MIN(arms+legs ) FROM limbs;
selecT @NAME := THING FROM LIMBS WHERE LEGS = 0;
SET @sum = 4+5;
SEELCT @sum;
SELECT MAX(arms+legs) FROM limbs;
mysql> SELECT MIN(arms+legs) FROM limbs;
INSERT into limbs (legs, arms, thing) values( ‘4’, ‘0’, ‘dog’);
SELECT 88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888 as ‘integer’;
select * from classics order by title DESC;
select * from classics order by title ASC;
SELECT author , title, year from classics order by year DESC;
SELECT author , title, year from classics order by year ASC;
select * from classics where year >1900 order by year desc;
SELECT COUNT(DISTINCT srcuser) FROM mail;
SELECT distinct category from classics;
SELECT * FROM classics WHERE DOB IS null;
SELECT * FROM classics WHERE DOB IS not null;
SELECT * from limbs cross join plantsbranchesflowers;
SELECT limbs.thing, limbs.legs from limbs INNER Join plantsbranchesflowers on limbs.arms = plantsbranchesflowers.branches;
SELECT * from limbs ;
SELECT * from limbs where legs >1 limit 10;
SELECT * from limbs order by thing limit 89 ;
SELECT * from limbs order by thing DESC limit 89 ;

SELECT limbs.thing, limbs.legs from limbs LEFT Join plantsbranchesflowers on limbs.arms =
plantsbranchesflowers.branches;
SELECT limbs.thing, limbs.legs from limbs RIGHT Join plantsbranchesflowers on
limbs.arms = plantsbranchesflowers.branches;
show PRIVILEGES;
select VERSION();
STORED PROCEDURE
create PROCEDURE KUNAL() SELECT * FROM LIMBS;
call KUNAL;
create PROCEDURE KUNALGAGNEJA() SELECT limbs.thing, limbs.legs from limbs INNER Join plantsbranchesflowers on limbs.arms = plantsbranchesflowers.branches;
CALL kunalGAGNEJA;
SELECT * FROM classics ORDER BY isbn limit 0, 9;
SELECT * FROM classics ORDER BY isbn limit 1,2;
create table limbstwo like limbs;
SELECT * FROM limbstwo;
INSERT INTO limbstwo SELECT * FROM limbs;
INSERT into limbsthree select * from limbs where legs >2 and thing like’a%’;
create table classicsthree select author, title, category from classics;

create table classicsthree like classics;
INSERT INTO classicsfour SELECT * FROM classics where year >1000 and author like ‘k%’;

create table classicsfive select * from classics;
select * from classicsfive;
create table classicssix select * from classics where false;
create table calssicsseven select category, year, author, isbn from classics;
create table classicseight ( id int not null auto_increment, primary key(id))select isbn, pages, year, category from classics;
create table classicstwelve ( id INT NOT NULL AUTO_INCREMENT primary key ) select category, year, pages, isbn from classics where year >1900;
create procedute kunaltwoo() create table classicsthirteen ( id INT NOT NULL AUTO_INCREMENT primary key ) select category, year, pages, isbn from classics where year >1900;
call kunaltwoo;
select * from classicsthirteen;
create temporary table mail select from classics; select count() from mail;
select connection_id();
SHOW CHARACTER SET;
show collation like ‘utf8%’;
SELECT “I’m asleep”, ‘He said, “Boo!”‘;
SELECT ‘I”m asleep’, ‘I\’m wide awake’;
SELECT “He said, “”Boo!”””, “And I said, \”Yikes!\””;
SELECT ‘He saidfsgfgfdgdfgdgd, “Boo!”‘;
SELECT 0x49276D261736C65657934758934758923759245; gives hex value
SELECT USER(), CHARSET(USER()), COLLATION(USER());
SELECT thing, UPPER(thing), LOWER(thing) FROM limbs;
SELECT 1+
/*
this is a
multiple-line comment
/ 1; SELECT 1 / this is an in-line comment */ + 1;
SELECT 1+1; # This comment continues to the end of line
SELECT 1+1; — This comment continues to the end of line
SELECT ‘cat’ = ‘cat’, ‘cat’ = ‘dog’, ‘cat’ <> ‘cat’, ‘cat’ <> ‘dog’;
select thing from limbs where thing not like ‘%ii%’;
select thing from limbs where thing like ‘%i%’;
select thing from limbs where thing regexp ‘^ce’;
select title, author, year, category from classics where year regexp ‘^.23’;
SELECT CONCAT(‘w3resource’,’.’,’com’, ‘kunal’, ‘gagneja’);
SELECT thing from limbs WHERE left(thing,1) >= ‘s’;
select concat (thing, ‘ends in “d”:’,if (right (thing, 1) = ‘d’,’yes’, ‘no’)) as ‘ends in “d”?’ from limbs;
UPDATE limbs SET thing = CONCAT(thing,’ide’);
update limbs set thing = left (thing ,char_length ( thing) -3);
SELECT CURTIME(), CURTIME(2), CURTIME(6);
select dob, date_FORMAT(dob,’%M %d, %Y’) FROM classics;
select dob, date_FORMAT(dob,’%M %d, %Y’) as date_in_view FROM classics;
select dob, date_FORMAT(dob,’%M %d, %Y’) as format1, date_format (dob, ‘%y, %d, %m’) as format4 FROM classics;
CREATE TABLE ttt (ts TIMESTAMP);
INSERT INTO ttt (ts) VALUES(‘2014-06-01 12:30:00’);
SELECT ts FROM ttt;
SET @dt = ‘2014-11-23 09:00:00’;
SELECT @dt AS Chicago,
-> CONVERT_TZ(@dt,’-06:00′,’+01:00′) AS Berlin,
-> CONVERT_TZ(@dt,’-06:00′,’+00:00′) AS London,
-> CONVERT_TZ(@dt,’-06:00′,’-07:00′) AS Edmonton,
-> CONVERT_TZ(@dt,’-06:00′,’+10:00′) AS Brisbane\G
SELECT CURDATE(), CURTIME(), NOW();
SELECT CURDATE(), YEAR(CURDATE()) AS year,MONTH(CURDATE()) AS month, MONTHNAME(CURDATE()) AS monthname,DAYOFMONTH(CURDATE()) AS day, DAYNAME(CURDATE()) AS dayname, NOW(), HOUR(NOW()) AS hour,MINUTE(NOW()) AS minute, SECOND(NOW()) AS second;
SELECT MAKETIME(10,30,58), MAKETIME(-5,0,11);
SET @d = ‘2014-02-28′, @t = ’13:10:05’;
SELECT @d, @t, CONCAT(@d,’ ‘,@t);
SELECT
CURDATE(),
UNIX_TIMESTAMP(CURDATE()),
FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE()))\G
SET @d1 = ‘2010-01-01’, @d2 = ‘2009-12-01’;
SELECT DATEDIFF(@d1,@d2) AS ‘d1 – d2’, DATEDIFF(@d2,@d1) AS ‘d2 – d1’;
SET @t1 = ’12:00:00′, @t2 = ’16:30:00′;
SELECT TIMEDIFF(@t1,@t2) AS ‘t1 – t2’, TIMEDIFF(@t2,@t1) AS ‘t2 – t1’;
SELECT
TIMESTAMPDIFF(MINUTE,@dt1,@dt2) AS minutes,
TIMESTAMPDIFF(HOUR,@dt1,@dt2) AS hours,
TIMESTAMPDIFF(DAY,@dt1,@dt2) AS days,
TIMESTAMPDIFF(WEEK,@dt1,@dt2) AS weeks,
TIMESTAMPDIFF(YEAR,@dt1,@dt2) AS years,
TIMESTAMPDIFF (SECONDS, @d1, @d2 AS seconds;
SET @dt1 = ‘1900-01-01 00:00:00’, @dt2 = ‘1910-01-01 00:00:00’;
SELECT
TIMESTAMPDIFF(MINUTE,@dt1,@dt2) AS minutes,
TIMESTAMPDIFF(HOUR,@dt1,@dt2) AS hours,
TIMESTAMPDIFF(DAY,@dt1,@dt2) AS days,
TIMESTAMPDIFF(WEEK,@dt1,@dt2) AS weeks,
TIMESTAMPDIFF(YEAR,@dt1,@dt2) AS years,
TIMESTAMPDIFF (SECOND, @d1, @d2) AS seconds;
SET @days = TO_DAYS(‘1884-01-01’) – TO_DAYS(‘1883-06-05’);
SELECT @days AS days, @days/7 AS weeks;

SET @dt1 = ‘1800-02-14 07:30:00’;
SET @dt2 = ‘1800-02-17 06:30:00’;
SET @interval =
((TO_DAYS(@dt2) – TO_DAYS(@dt1)) * 246060)

TIME_TO_SEC(@dt2) – TIME_TO_SEC(@dt1);
SELECT @interval AS seconds, SEC_TO_TIME(@interval) AS TIME;

SET @dt1 = ‘1800-02-14 07:30:00’;
SET @dt2 = ‘1800-02-17 06:30:00’;
SET @interval =
((TO_DAYS(@dt2) – TO_DAYS(@dt1)) * 246060)

TIME_TO_SEC(@dt2) – TIME_TO_SEC(@dt1);
SELECT @interval AS seconds, SEC_TO_TIME(@interval) AS TIME;
SET @t1 = ’12:00:00′, @t2 = ’15:30:00′;
SELECT ADDTIME(@t1,@t2);
SET @dt = ‘1984-03-01 12:00:00′, @t = ’12:00:00’;
SELECT ADDTIME(@dt,@t);
SELECT CURDATE(), DATE_ADD(CURDATE(),INTERVAL 3 DAY);
SELECT NOW(), DATE_ADD(NOW(),INTERVAL 60 HOUR);
SELECT CURDATE(), CURDATE() + INTERVAL 1 YEAR;
SELECT CURDATE(), DAYNAME(CURDATE());

SET @d = CURDATE();
SET @first = DATE_SUB(@d,INTERVAL DAYOFMONTH(@d)-1 DAY);
SELECT @d AS ‘starting date’,
@first AS ‘1st of month date’,
DAYNAME(@first) AS ‘1st of month day’;

SELECT
DAYOFYEAR(DATE_FORMAT(@d1,’%Y-12-31′)) AS ‘days in 2018’,
DAYOFYEAR(DATE_FORMAT(@d1,’%Y-12-31′)) AS ‘days in 2019’;

select DOB from classics where dob between ‘1900-01-01’ and ‘2999-12-31’;
select title, year, isbn, DOB from classics order by author;
select category, concat (year, ‘@’, pages) as yp, dob from classics where isbn>222 order by author;

1) can primany key be candidate key if required
A candidate key is a column or a set of columns that can qualify as a primary key in the database.
There can be multiple candidate keys in a database relation and each candidate can work as a primary key for the table.
A primary key is a column or a set of columns that identifies some record uniquely.
Only one candidate key can qualify as the primary key
2) types of keys in database
Key One or more columns in a database table that is used to sort and/or identify rows in a table.
e.g. if you were sorting people by the field salary then the salary field is the key.

Primary key. A primary key is a one or more fields that uniquely identifies a row in a table.
The primary key cannot be null (blank). The primary key is indexed.

Foreign key. A foreign key is a relationship between columns in two database tables (one of which is indexed)
designed to insure consistency of data e.g. each record in a CUSTOMER table contains the ID of the account manager
for that customer. In the ACCOUNT_MANAGER table the ID would typically be the primary key (indexed, unique, not null).
The ID field in the CUSTOMER table is the foreign key; only values for ACCOUNT_MANAGER.ID will be allowed in the CUSTOMER.ID field.

Composite key. A primary key composed of one or more columns e.g. a staff table STAFF contains the fields FNAME and LNAME for
first and last names respectively. The primary key can be formed using the fields (though not very advisable as I ll describe later).

Natural key. A composite primary key which is composed of attributes (fields)
which already exist in the real world e.g. First Name, Last Name, Social Security Number. .

Surrogate key. A primary key which is internally generated (typically auto-incremental integer value) that does not exist in the real
world i.e. ID=1 for Customer A and ID=2 for Customer B serves to uniquely identify the record but has no bearing the customer themselves
and is an attribute they will never (need to) be aware of.

Candidate key. A candidate key is a column or group of columns that can uniquely identify a row in the table without referring to any
other source. In a table which has multiple candidate keys one is selected to be the primary key. e.g. you could have a EMPLOYEE table
with a candidate key using FULL_NAME and another using DATE_OF_BIRTH.

Compound key A composite key consisting of two or more fields that uniquely describe a row in a table. The difference between compound
and candidate is that all of the fields in the compound key are foreign keys; in the candidate key one or more of the fields may be
foreign keys (but it is not mandatory). you could have a EMPLOYEE table with a candidate key using PASSPORT_NUMBER and another using
SOCIAL_SECURITY_NUMBER. In exclusion both can uniquely identify a row. Either can be used as a primary key (but not both since a table
can have only one primary key).
3)

full form of UTF8, utf32, utf 16. Unicode Transformation Format
how to delete all data but keep table;
truncate command questions TRUNCATE TABLE Student_details;
workbench for mysql;