Translate

Friday 17 September 2021

SQL Aliases as keyword in sql videos in Telugu 20

 https://youtu.be/yquX0PMNCvc

-------------------------------------------------------
The SQL UPDATE Statement
The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

 Be careful when updating records in a table! 
------------
Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!


Setting environment for using XAMPP for Windows.
Mounika@MOUNIKA-PC c:\xampp
# mysql -h localhost -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.18-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE vlrinst;
Database changed
MariaDB [vlrinst]> select 8 from emp
    -> ;
+---+
| 8 |
+---+
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
+---+
13 rows in set (0.101 sec)

MariaDB [vlrinst]> update emp set sal = sal+(sal*.3) ;
Query OK, 13 rows affected (0.148 sec)
Rows matched: 13  Changed: 13  Warnings: 0

MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  1 | venkat  | kphb      |   35 | 39000 |
|  2 | praveen | pune      |   28 | 45500 |
|  3 | mounika | hyderabad |   22 | 50700 |
|  4 | revathi | jntu      |   26 | 26000 |
|  5 | anji    | gutta     |   28 | 26000 |
|  6 | harika  | lb nagar  |   26 | 39130 |
|  7 | praveen | jntu      |   33 |   130 |
|  8 | pandu   | munipeda  |   35 | 39390 |
|  9 | pandu   | pune      |   23 | 51090 |
| 10 | venkat  | kphb      |   35 | 39000 |
| 11 | praveen | pune      |   28 | 45500 |
| 12 | mounika | hyderabad |   22 | 50700 |
| 13 | revathi | jntu      |   26 | 26000 |
+----+---------+-----------+------+-------+
13 rows in set (0.001 sec)

MariaDB [vlrinst]> update emp set sal = sal+(sal*.3) ;
Query OK, 13 rows affected (0.091 sec)
Rows matched: 13  Changed: 13  Warnings: 0

MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  1 | venkat  | kphb      |   35 | 50700 |
|  2 | praveen | pune      |   28 | 59150 |
|  3 | mounika | hyderabad |   22 | 65910 |
|  4 | revathi | jntu      |   26 | 33800 |
|  5 | anji    | gutta     |   28 | 33800 |
|  6 | harika  | lb nagar  |   26 | 50869 |
|  7 | praveen | jntu      |   33 |   169 |
|  8 | pandu   | munipeda  |   35 | 51207 |
|  9 | pandu   | pune      |   23 | 66417 |
| 10 | venkat  | kphb      |   35 | 50700 |
| 11 | praveen | pune      |   28 | 59150 |
| 12 | mounika | hyderabad |   22 | 65910 |
| 13 | revathi | jntu      |   26 | 33800 |
+----+---------+-----------+------+-------+
13 rows in set (0.001 sec)

MariaDB [vlrinst]> update emp set name='ramesh' where id=1;
Query OK, 1 row affected (0.124 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  1 | ramesh  | kphb      |   35 | 50700 |
|  2 | praveen | pune      |   28 | 59150 |
|  3 | mounika | hyderabad |   22 | 65910 |
|  4 | revathi | jntu      |   26 | 33800 |
|  5 | anji    | gutta     |   28 | 33800 |
|  6 | harika  | lb nagar  |   26 | 50869 |
|  7 | praveen | jntu      |   33 |   169 |
|  8 | pandu   | munipeda  |   35 | 51207 |
|  9 | pandu   | pune      |   23 | 66417 |
| 10 | venkat  | kphb      |   35 | 50700 |
| 11 | praveen | pune      |   28 | 59150 |
| 12 | mounika | hyderabad |   22 | 65910 |
| 13 | revathi | jntu      |   26 | 33800 |
+----+---------+-----------+------+-------+
13 rows in set (0.001 sec)

MariaDB [vlrinst]> update emp set name='ramesh1',age=33,loc='jntu' where id=1;
Query OK, 1 row affected (0.068 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  1 | ramesh1 | jntu      |   33 | 50700 |
|  2 | praveen | pune      |   28 | 59150 |
|  3 | mounika | hyderabad |   22 | 65910 |
|  4 | revathi | jntu      |   26 | 33800 |
|  5 | anji    | gutta     |   28 | 33800 |
|  6 | harika  | lb nagar  |   26 | 50869 |
|  7 | praveen | jntu      |   33 |   169 |
|  8 | pandu   | munipeda  |   35 | 51207 |
|  9 | pandu   | pune      |   23 | 66417 |
| 10 | venkat  | kphb      |   35 | 50700 |
| 11 | praveen | pune      |   28 | 59150 |
| 12 | mounika | hyderabad |   22 | 65910 |
| 13 | revathi | jntu      |   26 | 33800 |
+----+---------+-----------+------+-------+
13 rows in set (0.000 sec)

MariaDB [vlrinst]> update emp set age=29 where age=26;
Query OK, 3 rows affected (0.059 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [vlrinst]> update emp set sal=sal+(sal*.2) ;
Query OK, 13 rows affected (0.092 sec)
Rows matched: 13  Changed: 13  Warnings: 0

MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  1 | ramesh1 | jntu      |   33 | 60840 |
|  2 | praveen | pune      |   28 | 70980 |
|  3 | mounika | hyderabad |   22 | 79092 |
|  4 | revathi | jntu      |   29 | 40560 |
|  5 | anji    | gutta     |   28 | 40560 |
|  6 | harika  | lb nagar  |   29 | 61043 |
|  7 | praveen | jntu      |   33 |   203 |
|  8 | pandu   | munipeda  |   35 | 61448 |
|  9 | pandu   | pune      |   23 | 79700 |
| 10 | venkat  | kphb      |   35 | 60840 |
| 11 | praveen | pune      |   28 | 70980 |
| 12 | mounika | hyderabad |   22 | 79092 |
| 13 | revathi | jntu      |   29 | 40560 |
+----+---------+-----------+------+-------+
13 rows in set (0.001 sec)

MariaDB [vlrinst]> update emp set sal=sal+10000 where sal <41000 ;
Query OK, 4 rows affected (0.074 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  1 | ramesh1 | jntu      |   33 | 60840 |
|  2 | praveen | pune      |   28 | 70980 |
|  3 | mounika | hyderabad |   22 | 79092 |
|  4 | revathi | jntu      |   29 | 50560 |
|  5 | anji    | gutta     |   28 | 50560 |
|  6 | harika  | lb nagar  |   29 | 61043 |
|  7 | praveen | jntu      |   33 | 10203 |
|  8 | pandu   | munipeda  |   35 | 61448 |
|  9 | pandu   | pune      |   23 | 79700 |
| 10 | venkat  | kphb      |   35 | 60840 |
| 11 | praveen | pune      |   28 | 70980 |
| 12 | mounika | hyderabad |   22 | 79092 |
| 13 | revathi | jntu      |   29 | 50560 |
+----+---------+-----------+------+-------+
13 rows in set (0.001 sec)

MariaDB [vlrinst]>

Posted by at No comments:
Labels:

SQL WHERE Clause in sql videos in Telugu 19

 https://youtu.be/2BnymAPafKI

-----------------------------------------
SQL Aliases
SQL aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable.

An alias only exists for the duration of that query.

An alias is created with the AS keyword.

Alias Column Syntax
SELECT column_name AS alias_name
FROM table_name;
Alias Table Syntax
SELECT column_name(s)
FROM table_name AS alias_name;
--------------
It requires double quotation marks or square brackets if the alias name contains spaces:
Posted by at No comments:
Labels:

Select and distinct statements in sql videos in Telugu 18

 https://youtu.be/G7V0lnEkD84

------------------------------------
| hyderabad | mounika |
| jntu      | revathi |
| gutta     | anji    |
| lb nagar  | harika  |
| jntu      | praveen |
| munipeda  | pandu   |
| pune      | pandu   |
+-----------+---------+
9 rows in set (0.011 sec)

MariaDB [vlrinst]> INSERT INTO emp(name, loc, age,sal)
    -> VALUES ('venkat', 'kphb', 35,30000),
    ->        ('praveen', 'pune', 28,35000),
    ->        ('mounika', 'hyderabad', 22,39000),
    ->        ('revathi', 'jntu', 26,20000),
    ->        ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 5
MariaDB [vlrinst]> INSERT INTO emp(name, loc, age,sal)
    -> VALUES ('venkat', 'kphb', 35,30000),
    ->        ('praveen', 'pune', 28,35000),
    ->        ('mounika', 'hyderabad', 22,39000),
    ->        ('revathi', 'jntu', 26,20000),
    -> INSERT INTO emp(name, loc, age,sal)
    -> VALUES ('venkat', 'kphb', 35,30000),
    ->        ('praveen', 'pune', 28,35000),
    ->        ('mounika', 'hyderabad', 22,39000),
    ->        ('revathi', 'jntu', 26,20000)
    ->        ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO emp(name, loc, age,sa
l)
VALUES ('venkat', 'kphb', 35,30000),
   ...' at line 6
MariaDB [vlrinst]> INSERT INTO emp(name, loc, age,sal)
    -> VALUES ('venkat', 'kphb', 35,30000),
    ->        ('praveen', 'pune', 28,35000),
    ->        ('mounika', 'hyderabad', 22,39000),
    ->        ('revathi', 'jntu', 26,20000)
    ->        ;
Query OK, 4 rows affected (0.105 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [vlrinst]> select * from emp
    -> ;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  1 | venkat  | kphb      |   35 | 30000 |
|  2 | praveen | pune      |   28 | 35000 |
|  3 | mounika | hyderabad |   22 | 39000 |
|  4 | revathi | jntu      |   26 | 20000 |
|  5 | anji    | gutta     |   28 | 20000 |
|  6 | harika  | lb nagar  |   26 | 30100 |
|  7 | praveen | jntu      |   33 |   100 |
|  8 | pandu   | munipeda  |   35 | 30300 |
|  9 | pandu   | pune      |   23 | 39300 |
| 10 | venkat  | kphb      |   35 | 30000 |
| 11 | praveen | pune      |   28 | 35000 |
| 12 | mounika | hyderabad |   22 | 39000 |
| 13 | revathi | jntu      |   26 | 20000 |
+----+---------+-----------+------+-------+
13 rows in set (0.001 sec)

MariaDB [vlrinst]> select distinct loc,name form emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'emp' at line 1
MariaDB [vlrinst]> select distinct loc,name from emp;
+-----------+---------+
| loc       | name    |
+-----------+---------+
| kphb      | venkat  |
| pune      | praveen |
| hyderabad | mounika |
| jntu      | revathi |
| gutta     | anji    |
| lb nagar  | harika  |
| jntu      | praveen |
| munipeda  | pandu   |
| pune      | pandu   |
+-----------+---------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> select distinct loc,name,id from emp;
+-----------+---------+----+
| loc       | name    | id |
+-----------+---------+----+
| kphb      | venkat  |  1 |
| pune      | praveen |  2 |
| hyderabad | mounika |  3 |
| jntu      | revathi |  4 |
| gutta     | anji    |  5 |
| lb nagar  | harika  |  6 |
| jntu      | praveen |  7 |
| munipeda  | pandu   |  8 |
| pune      | pandu   |  9 |
| kphb      | venkat  | 10 |
| pune      | praveen | 11 |
| hyderabad | mounika | 12 |
| jntu      | revathi | 13 |
+-----------+---------+----+
13 rows in set (0.001 sec)

MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  1 | venkat  | kphb      |   35 | 30000 |
|  2 | praveen | pune      |   28 | 35000 |
|  3 | mounika | hyderabad |   22 | 39000 |
|  4 | revathi | jntu      |   26 | 20000 |
|  5 | anji    | gutta     |   28 | 20000 |
|  6 | harika  | lb nagar  |   26 | 30100 |
|  7 | praveen | jntu      |   33 |   100 |
|  8 | pandu   | munipeda  |   35 | 30300 |
|  9 | pandu   | pune      |   23 | 39300 |
| 10 | venkat  | kphb      |   35 | 30000 |
| 11 | praveen | pune      |   28 | 35000 |
| 12 | mounika | hyderabad |   22 | 39000 |
| 13 | revathi | jntu      |   26 | 20000 |
+----+---------+-----------+------+-------+
13 rows in set (0.001 sec)

MariaDB [vlrinst]> select id,name from emp where sal>30000;
+----+---------+
| id | name    |
+----+---------+
|  2 | praveen |
|  3 | mounika |
|  6 | harika  |
|  8 | pandu   |
|  9 | pandu   |
| 11 | praveen |
| 12 | mounika |
+----+---------+
7 rows in set (0.074 sec)

MariaDB [vlrinst]> select id,name from emp where loc = 'pune';
+----+---------+
| id | name    |
+----+---------+
|  2 | praveen |
|  9 | pandu   |
| 11 | praveen |
+----+---------+
3 rows in set (0.129 sec)

MariaDB [vlrinst]> select id,name from emp where name <> 'praveen';
+----+---------+
| id | name    |
+----+---------+
|  1 | venkat  |
|  3 | mounika |
|  4 | revathi |
|  5 | anji    |
|  6 | harika  |
|  8 | pandu   |
|  9 | pandu   |
| 10 | venkat  |
| 12 | mounika |
| 13 | revathi |
+----+---------+
10 rows in set (0.001 sec)

MariaDB [vlrinst]> select id,name from emp where name <> 'Praveen';
+----+---------+
| id | name    |
+----+---------+
|  1 | venkat  |
|  3 | mounika |
|  4 | revathi |
|  5 | anji    |
|  6 | harika  |
|  8 | pandu   |
|  9 | pandu   |
| 10 | venkat  |
| 12 | mounika |
| 13 | revathi |
+----+---------+
10 rows in set (0.001 sec)

MariaDB [vlrinst]> select id,name from emp where sal between 30000 and 35000;
+----+---------+
| id | name    |
+----+---------+
|  1 | venkat  |
|  2 | praveen |
|  6 | harika  |
|  8 | pandu   |
| 10 | venkat  |
| 11 | praveen |
+----+---------+
6 rows in set (0.045 sec)

MariaDB [vlrinst]> select id,name from emp where sal between 30000 and 32000;
+----+--------+
| id | name   |
+----+--------+
|  1 | venkat |
|  6 | harika |
|  8 | pandu  |
| 10 | venkat |
+----+--------+
4 rows in set (0.000 sec)

MariaDB [vlrinst]> select id,name,sal from emp where sal between 30000 and 32000;
+----+--------+-------+
| id | name   | sal   |
+----+--------+-------+
|  1 | venkat | 30000 |
|  6 | harika | 30100 |
|  8 | pandu  | 30300 |
| 10 | venkat | 30000 |
+----+--------+-------+
4 rows in set (0.000 sec)

MariaDB [vlrinst]> select id,name,sal from emp where name like 'h%';
+----+--------+-------+
| id | name   | sal   |
+----+--------+-------+
|  6 | harika | 30100 |
+----+--------+-------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select id,name,sal from emp where name like 'r%';
+----+---------+-------+
| id | name    | sal   |
+----+---------+-------+
|  4 | revathi | 20000 |
| 13 | revathi | 20000 |
+----+---------+-------+
2 rows in set (0.000 sec)

MariaDB [vlrinst]> select id,name,sal from emp where name like '%r';
Empty set (0.001 sec)

MariaDB [vlrinst]> select id,name,sal from emp where name like '%n';
+----+---------+-------+
| id | name    | sal   |
+----+---------+-------+
|  2 | praveen | 35000 |
|  7 | praveen |   100 |
| 11 | praveen | 35000 |
+----+---------+-------+
3 rows in set (0.000 sec)

MariaDB [vlrinst]> select id,name,sal from emp where loc in('pune','jntu');
+----+---------+-------+
| id | name    | sal   |
+----+---------+-------+
|  2 | praveen | 35000 |
|  4 | revathi | 20000 |
|  7 | praveen |   100 |
|  9 | pandu   | 39300 |
| 11 | praveen | 35000 |
| 13 | revathi | 20000 |
+----+---------+-------+
6 rows in set (0.031 sec)

MariaDB [vlrinst]> select id,name,sal,loc from emp where loc in('pune','jntu');
+----+---------+-------+------+
| id | name    | sal   | loc  |
+----+---------+-------+------+
|  2 | praveen | 35000 | pune |
|  4 | revathi | 20000 | jntu |
|  7 | praveen |   100 | jntu |
|  9 | pandu   | 39300 | pune |
| 11 | praveen | 35000 | pune |
| 13 | revathi | 20000 | jntu |
+----+---------+-------+------+
6 rows in set (0.000 sec)

MariaDB [vlrinst]> select id,name,sal,loc from emp where loc in('pune','jntu','kphb');
+----+---------+-------+------+
| id | name    | sal   | loc  |
+----+---------+-------+------+
|  1 | venkat  | 30000 | kphb |
|  2 | praveen | 35000 | pune |
|  4 | revathi | 20000 | jntu |
|  7 | praveen |   100 | jntu |
|  9 | pandu   | 39300 | pune |
| 10 | venkat  | 30000 | kphb |
| 11 | praveen | 35000 | pune |
| 13 | revathi | 20000 | jntu |
+----+---------+-------+------+
8 rows in set (0.000 sec)

MariaDB [vlrinst]> select id,name,sal,loc from emp where loc in('pune','jntu','kphb','sjks');
+----+---------+-------+------+
| id | name    | sal   | loc  |
+----+---------+-------+------+
|  1 | venkat  | 30000 | kphb |
|  2 | praveen | 35000 | pune |
|  4 | revathi | 20000 | jntu |
|  7 | praveen |   100 | jntu |
|  9 | pandu   | 39300 | pune |
| 10 | venkat  | 30000 | kphb |
| 11 | praveen | 35000 | pune |
| 13 | revathi | 20000 | jntu |
+----+---------+-------+------+
8 rows in set (0.001 sec)

MariaDB [vlrinst]>
Posted by at No comments:
Labels:

What are CRUD Operations in sql videos in Telugu 17

 https://youtu.be/y6K-Proi1RA

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

Setting environment for using XAMPP for Windows.
Mounika@MOUNIKA-PC c:\xampp
# mysql -h localhost -u root;
ERROR 1045 (28000): Access denied for user 'root;'@'localhost' (using password: NO)

Mounika@MOUNIKA-PC c:\xampp
# USE vlrinst;
'USE' is not recognized as an internal or external command,
operable program or batch file.

Mounika@MOUNIKA-PC c:\xampp
# mysql -h localhost -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.4.18-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE vlrinst;
Database changed
MariaDB [vlrinst]> show tables;
+-------------------+
| Tables_in_vlrinst |
+-------------------+
| emp               |
+-------------------+
1 row in set (0.098 sec)

MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  1 | venkat  | kphb      |   35 | 30000 |
|  2 | praveen | pune      |   28 | 35000 |
|  3 | mounika | hyderabad |   22 | 39000 |
|  4 | revathi | jntu      |   26 | 20000 |
|  5 | anji    | gutta     |   28 | 20000 |
|  6 | harika  | lb nagar  |   26 | 30100 |
|  7 | praveen | jntu      |   33 |   100 |
|  8 | pandu   | munipeda  |   35 | 30300 |
|  9 | pandu   | pune      |   23 | 39300 |
+----+---------+-----------+------+-------+
9 rows in set (0.154 sec)

MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  1 | venkat  | kphb      |   35 | 30000 |
|  2 | praveen | pune      |   28 | 35000 |
|  3 | mounika | hyderabad |   22 | 39000 |
|  4 | revathi | jntu      |   26 | 20000 |
|  5 | anji    | gutta     |   28 | 20000 |
|  6 | harika  | lb nagar  |   26 | 30100 |
|  7 | praveen | jntu      |   33 |   100 |
|  8 | pandu   | munipeda  |   35 | 30300 |
|  9 | pandu   | pune      |   23 | 39300 |
+----+---------+-----------+------+-------+
9 rows in set (0.002 sec)

MariaDB [vlrinst]> select name,loc from emp;
+---------+-----------+
| name    | loc       |
+---------+-----------+
| venkat  | kphb      |
| praveen | pune      |
| mounika | hyderabad |
| revathi | jntu      |
| anji    | gutta     |
| harika  | lb nagar  |
| praveen | jntu      |
| pandu   | munipeda  |
| pandu   | pune      |
+---------+-----------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> select age from emp;
+------+
| age  |
+------+
|   35 |
|   28 |
|   22 |
|   26 |
|   28 |
|   26 |
|   33 |
|   35 |
|   23 |
+------+
9 rows in set (0.000 sec)

MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  1 | venkat  | kphb      |   35 | 30000 |
|  2 | praveen | pune      |   28 | 35000 |
|  3 | mounika | hyderabad |   22 | 39000 |
|  4 | revathi | jntu      |   26 | 20000 |
|  5 | anji    | gutta     |   28 | 20000 |
|  6 | harika  | lb nagar  |   26 | 30100 |
|  7 | praveen | jntu      |   33 |   100 |
|  8 | pandu   | munipeda  |   35 | 30300 |
|  9 | pandu   | pune      |   23 | 39300 |
+----+---------+-----------+------+-------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> select id,name,age,sal from emp;
+----+---------+------+-------+
| id | name    | age  | sal   |
+----+---------+------+-------+
|  1 | venkat  |   35 | 30000 |
|  2 | praveen |   28 | 35000 |
|  3 | mounika |   22 | 39000 |
|  4 | revathi |   26 | 20000 |
|  5 | anji    |   28 | 20000 |
|  6 | harika  |   26 | 30100 |
|  7 | praveen |   33 |   100 |
|  8 | pandu   |   35 | 30300 |
|  9 | pandu   |   23 | 39300 |
+----+---------+------+-------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> select name,sal,id,name from emp;
+---------+-------+----+---------+
| name    | sal   | id | name    |
+---------+-------+----+---------+
| venkat  | 30000 |  1 | venkat  |
| praveen | 35000 |  2 | praveen |
| mounika | 39000 |  3 | mounika |
| revathi | 20000 |  4 | revathi |
| anji    | 20000 |  5 | anji    |
| harika  | 30100 |  6 | harika  |
| praveen |   100 |  7 | praveen |
| pandu   | 30300 |  8 | pandu   |
| pandu   | 39300 |  9 | pandu   |
+---------+-------+----+---------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  1 | venkat  | kphb      |   35 | 30000 |
|  2 | praveen | pune      |   28 | 35000 |
|  3 | mounika | hyderabad |   22 | 39000 |
|  4 | revathi | jntu      |   26 | 20000 |
|  5 | anji    | gutta     |   28 | 20000 |
|  6 | harika  | lb nagar  |   26 | 30100 |
|  7 | praveen | jntu      |   33 |   100 |
|  8 | pandu   | munipeda  |   35 | 30300 |
|  9 | pandu   | pune      |   23 | 39300 |
+----+---------+-----------+------+-------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> select distinct loc form emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'emp' at line 1
MariaDB [vlrinst]> select distinct loc from emp;
+-----------+
| loc       |
+-----------+
| kphb      |
| pune      |
| hyderabad |
| jntu      |
| gutta     |
| lb nagar  |
| munipeda  |
+-----------+
7 rows in set (0.016 sec)

MariaDB [vlrinst]> select distinct loc,name from emp;
+-----------+---------+
| loc       | name    |
+-----------+---------+
| kphb      | venkat  |
| pune      | praveen |
| hyderabad | mounika |
| jntu      | revathi |
| gutta     | anji    |
| lb nagar  | harika  |
| jntu      | praveen |
| munipeda  | pandu   |
| pune      | pandu   |
+-----------+---------+
9 rows in set (0.011 sec)

MariaDB [vlrinst]> INSERT INTO emp(name, loc, age,sal)
    -> VALUES ('venkat', 'kphb', 35,30000),
    ->        ('praveen', 'pune', 28,35000),
    ->        ('mounika', 'hyderabad', 22,39000),
    ->        ('revathi', 'jntu', 26,20000),
    ->        ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 5
MariaDB [vlrinst]> INSERT INTO emp(name, loc, age,sal)
    -> VALUES ('venkat', 'kphb', 35,30000),
    ->        ('praveen', 'pune', 28,35000),
    ->        ('mounika', 'hyderabad', 22,39000),
    ->        ('revathi', 'jntu', 26,20000),
    -> INSERT INTO emp(name, loc, age,sal)
    -> VALUES ('venkat', 'kphb', 35,30000),
    ->        ('praveen', 'pune', 28,35000),
    ->        ('mounika', 'hyderabad', 22,39000),
    ->        ('revathi', 'jntu', 26,20000)
    ->        ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO emp(name, loc, age,sa
l)
VALUES ('venkat', 'kphb', 35,30000),
   ...' at line 6
MariaDB [vlrinst]> INSERT INTO emp(name, loc, age,sal)
    -> VALUES ('venkat', 'kphb', 35,30000),
    ->        ('praveen', 'pune', 28,35000),
    ->        ('mounika', 'hyderabad', 22,39000),
    ->        ('revathi', 'jntu', 26,20000)
    ->        ;
Query OK, 4 rows affected (0.105 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [vlrinst]> select * from emp
    -> ;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  1 | venkat  | kphb      |   35 | 30000 |
|  2 | praveen | pune      |   28 | 35000 |
|  3 | mounika | hyderabad |   22 | 39000 |
|  4 | revathi | jntu      |   26 | 20000 |
|  5 | anji    | gutta     |   28 | 20000 |
|  6 | harika  | lb nagar  |   26 | 30100 |
|  7 | praveen | jntu      |   33 |   100 |
|  8 | pandu   | munipeda  |   35 | 30300 |
|  9 | pandu   | pune      |   23 | 39300 |
| 10 | venkat  | kphb      |   35 | 30000 |
| 11 | praveen | pune      |   28 | 35000 |
| 12 | mounika | hyderabad |   22 | 39000 |
| 13 | revathi | jntu      |   26 | 20000 |
+----+---------+-----------+------+-------+
13 rows in set (0.001 sec)

MariaDB [vlrinst]> select distinct loc,name form emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'emp' at line 1
MariaDB [vlrinst]> select distinct loc,name from emp;
+-----------+---------+
| loc       | name    |
+-----------+---------+
| kphb      | venkat  |
| pune      | praveen |
| hyderabad | mounika |
| jntu      | revathi |
| gutta     | anji    |
| lb nagar  | harika  |
| jntu      | praveen |
| munipeda  | pandu   |
| pune      | pandu   |
+-----------+---------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> select distinct loc,name,id from emp;
+-----------+---------+----+
| loc       | name    | id |
+-----------+---------+----+
| kphb      | venkat  |  1 |
| pune      | praveen |  2 |
| hyderabad | mounika |  3 |
| jntu      | revathi |  4 |
| gutta     | anji    |  5 |
| lb nagar  | harika  |  6 |
| jntu      | praveen |  7 |
| munipeda  | pandu   |  8 |
| pune      | pandu   |  9 |
| kphb      | venkat  | 10 |
| pune      | praveen | 11 |
| hyderabad | mounika | 12 |
| jntu      | revathi | 13 |
+-----------+---------+----+
13 rows in set (0.001 sec)

MariaDB [vlrinst]>
Posted by at No comments:
Labels:

How to use Unique Constraint table in telugu 16

 https://youtu.be/ZLT9ERzltY4

---------------------------------------
CRUD is an acronym that comes from the world of computer programming and refers to the four functions that are considered necessary to implement a persistent storage applic
ation: create, read, update and delete.
-----------------
CREATE TABLE emp 
  ( 
     id INT NOT NULL AUTO_INCREMENT, 
     name   VARCHAR(100), 
     loc  VARCHAR(100), 
     age    INT, 
     sal     INT,
     PRIMARY KEY (id) 
  ); 
--------------
INSERT INTO emp(name, loc, age,sal) 
VALUES ('venkat', 'kphb', 35,30000),
       ('praveen', 'pune', 28,35000),
       ('mounika', 'hyderabad', 22,39000),
       ('revathi', 'jntu', 26,20000),
       ('anji', 'gutta', 28,20000),
       ('harika', 'lb nagar', 26,30100),
       ('praveen', 'jntu', 33,100),
       ('pandu', 'munipeda', 35,30300),
       ('pandu', 'pune', 23,39300);

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

The SQL SELECT Statement
The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax
SELECT column1, column2, ...
FROM table_name;
Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;
---------------------
The SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2, ...
FROM table_name;
--------------
The SQL WHERE Clause
The WHERE clause is used to filter records.

It is used to extract only those records that fulfill a specified condition.

WHERE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.!

-----------------
Text Fields vs. Numeric Fields
SQL requires single quotes around text values (most database systems will also allow double quotes).

However, numeric fields should not be enclosed in quotes:
--------------
Operators in The WHERE Clause
The following operators can be used in the WHERE clause:

Operator Description
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN Between a certain range( BETWEEN 50 AND 60;)
LIKE Search for a pattern (City LIKE 's%';)
IN To specify multiple possible values for a column )City IN ('Paris','London');)
---------------------
|  8 | pandu   | munipeda  |   35 | 30300 |
+----+---------+-----------+------+-------+
8 rows in set (0.01 sec)

MariaDB [vlrinst]> select name,id,loc,sal,age from emp;
+---------+----+-----------+-------+------+
| name    | id | loc       | sal   | age  |
+---------+----+-----------+-------+------+
| venkat  |  1 | kphb      | 30000 |   35 |
| praveen |  2 | pune      | 35000 |   28 |
| mounika |  3 | hyderabad | 39000 |   22 |
| revathi |  4 | jntu      | 20000 |   26 |
| anji    |  5 | gutta     | 20000 |   28 |
| harika  |  6 | lb nagar  | 30100 |   26 |
| praveen |  7 | jntu      |   100 |   33 |
| pandu   |  8 | munipeda  | 30300 |   35 |
+---------+----+-----------+-------+------+
8 rows in set (0.00 sec)

MariaDB [vlrinst]> select name from emp;
+---------+
| name    |
+---------+
| venkat  |
| praveen |
| mounika |
| revathi |
| anji    |
| harika  |
| praveen |
| pandu   |
+---------+
8 rows in set (0.00 sec)

MariaDB [vlrinst]> select name,sal from emp;
+---------+-------+
| name    | sal   |
+---------+-------+
| venkat  | 30000 |
| praveen | 35000 |
| mounika | 39000 |
| revathi | 20000 |
| anji    | 20000 |
| harika  | 30100 |
| praveen |   100 |
| pandu   | 30300 |
+---------+-------+
8 rows in set (0.00 sec)

MariaDB [vlrinst]> select distinct name from emp;\
+---------+
| name    |
+---------+
| venkat  |
| praveen |
| mounika |
| revathi |
| anji    |
| harika  |
| pandu   |
+---------+
7 rows in set (0.05 sec)

MariaDB [vlrinst]> select distinct name,loc from emp;\
+---------+-----------+
| name    | loc       |
+---------+-----------+
| venkat  | kphb      |
| praveen | pune      |
| mounika | hyderabad |
| revathi | jntu      |
| anji    | gutta     |
| harika  | lb nagar  |
| praveen | jntu      |
| pandu   | munipeda  |
+---------+-----------+
8 rows in set (0.00 sec)

MariaDB [vlrinst]> select * from emp where loc='jntu';
+----+---------+------+------+-------+
| id | name    | loc  | age  | sal   |
+----+---------+------+------+-------+
|  4 | revathi | jntu |   26 | 20000 |
|  7 | praveen | jntu |   33 |   100 |
+----+---------+------+------+-------+
2 rows in set (0.13 sec)

MariaDB [vlrinst]> select * from emp where age=26;
+----+---------+----------+------+-------+
| id | name    | loc      | age  | sal   |
+----+---------+----------+------+-------+
|  4 | revathi | jntu     |   26 | 20000 |
|  6 | harika  | lb nagar |   26 | 30100 |
+----+---------+----------+------+-------+
2 rows in set (0.00 sec)

MariaDB [vlrinst]> select * from emp where age>26;
+----+---------+----------+------+-------+
| id | name    | loc      | age  | sal   |
+----+---------+----------+------+-------+
|  1 | venkat  | kphb     |   35 | 30000 |
|  2 | praveen | pune     |   28 | 35000 |
|  5 | anji    | gutta    |   28 | 20000 |
|  7 | praveen | jntu     |   33 |   100 |
|  8 | pandu   | munipeda |   35 | 30300 |
+----+---------+----------+------+-------+
5 rows in set (0.02 sec)

MariaDB [vlrinst]> select * from emp where age<26;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  3 | mounika | hyderabad |   22 | 39000 |
+----+---------+-----------+------+-------+
1 row in set (0.00 sec)

MariaDB [vlrinst]> select name from emp where age<26;
+---------+
| name    |
+---------+
| mounika |
+---------+
1 row in set (0.00 sec)

MariaDB [vlrinst]> select name from emp where sal<20000;
+---------+
| name    |
+---------+
| praveen |
+---------+
1 row in set (0.00 sec)

MariaDB [vlrinst]> select name,sal from emp where sal<20000;
+---------+------+
| name    | sal  |
+---------+------+
| praveen |  100 |
+---------+------+
1 row in set (0.00 sec)

MariaDB [vlrinst]> select name,sal from emp where sal<>100;
+---------+-------+
| name    | sal   |
+---------+-------+
| venkat  | 30000 |
| praveen | 35000 |
| mounika | 39000 |
| revathi | 20000 |
| anji    | 20000 |
| harika  | 30100 |
| pandu   | 30300 |
+---------+-------+
7 rows in set (0.00 sec)

MariaDB [vlrinst]> select name,sal from emp where sal<>20000;
+---------+-------+
| name    | sal   |
+---------+-------+
| venkat  | 30000 |
| praveen | 35000 |
| mounika | 39000 |
| harika  | 30100 |
| praveen |   100 |
| pandu   | 30300 |
+---------+-------+
6 rows in set (0.00 sec)

MariaDB [vlrinst]> select name,sal from emp where sal between 20000 and 30300;
+---------+-------+
| name    | sal   |
+---------+-------+
| venkat  | 30000 |
| revathi | 20000 |
| anji    | 20000 |
| harika  | 30100 |
| pandu   | 30300 |
+---------+-------+
5 rows in set (0.00 sec)

MariaDB [vlrinst]> select name from emp where name like 'v%'
    -> ;
+--------+
| name   |
+--------+
| venkat |
+--------+
1 row in set (0.00 sec)

MariaDB [vlrinst]> select name from emp where name like 'p%';
+---------+
| name    |
+---------+
| praveen |
| praveen |
| pandu   |
+---------+
3 rows in set (0.00 sec)

MariaDB [vlrinst]> select name from emp where name like '%t';
+--------+
| name   |
+--------+
| venkat |
+--------+
1 row in set (0.00 sec)

MariaDB [vlrinst]> select name from emp where name like '%n';
+---------+
| name    |
+---------+
| praveen |
| praveen |
+---------+
2 rows in set (0.00 sec)

MariaDB [vlrinst]> select name from emp where name like '%a';
+---------+
| name    |
+---------+
| mounika |
| harika  |
+---------+
2 rows in set (0.00 sec)

MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
|  1 | venkat  | kphb      |   35 | 30000 |
|  2 | praveen | pune      |   28 | 35000 |
|  3 | mounika | hyderabad |   22 | 39000 |
|  4 | revathi | jntu      |   26 | 20000 |
|  5 | anji    | gutta     |   28 | 20000 |
|  6 | harika  | lb nagar  |   26 | 30100 |
|  7 | praveen | jntu      |   33 |   100 |
|  8 | pandu   | munipeda  |   35 | 30300 |
+----+---------+-----------+------+-------+
8 rows in set (0.00 sec)

MariaDB [vlrinst]> select * from emp where loc in ('kphb','pune');
+----+---------+------+------+-------+
| id | name    | loc  | age  | sal   |
+----+---------+------+------+-------+
|  1 | venkat  | kphb |   35 | 30000 |
|  2 | praveen | pune |   28 | 35000 |
+----+---------+------+------+-------+
2 rows in set (0.00 sec)

MariaDB [vlrinst]> select * from emp where loc in ('kphb','pune','munipeda');
+----+---------+----------+------+-------+
| id | name    | loc      | age  | sal   |
+----+---------+----------+------+-------+
|  1 | venkat  | kphb     |   35 | 30000 |
|  2 | praveen | pune     |   28 | 35000 |
|  8 | pandu   | munipeda |   35 | 30300 |
+----+---------+----------+------+-------+
3 rows in set (0.00 sec)

MariaDB [vlrinst]> select * from emp where loc in ('kphb','Pune','munipeda');
+----+---------+----------+------+-------+
| id | name    | loc      | age  | sal   |
+----+---------+----------+------+-------+
|  1 | venkat  | kphb     |   35 | 30000 |
|  2 | praveen | pune     |   28 | 35000 |
|  8 | pandu   | munipeda |   35 | 30300 |
+----+---------+----------+------+-------+
3 rows in set (0.00 sec)

MariaDB [vlrinst]> select ID from emp;
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
+----+
8 rows in set (0.00 sec)

MariaDB [vlrinst]> select ID from emp where name like 'V%';
+----+
| ID |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

MariaDB [vlrinst]> select ID,name from emp where name like 'V%';
+----+--------+
| ID | name   |
+----+--------+
|  1 | venkat |
+----+--------+
1 row in set (0.00 sec)

MariaDB [vlrinst]>
Posted by at No comments:
Labels:

How to create SQL PRIMARY KEY Constraint table in telugu 15

 https://youtu.be/UcX1RcgvOhk

---------------------------------------
SQL Constraints
SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Uniquely identifies a row/record in another table
CHECK - Ensures that all values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column when no value is specified
INDEX - Used to create and retrieve data from the database very quickly
--------------------------
SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);
-------------------
SQL UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
);

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

Setting environment for using XAMPP for Windows.
venkat@VENKAT-PC c:\xampp
# mysql -h localhost -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.37-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE vlrinst;
Database changed
MariaDB [vlrinst]> show tables
    -> ;
+-------------------+
| Tables_in_vlrinst |
+-------------------+
| ram               |
| ram1              |
| ram2              |
+-------------------+
3 rows in set (0.07 sec)

MariaDB [vlrinst]> create table r1(
    -> id int,
    -> name varchar(30),
    -> age int,
    -> check(age>=18)
    -> );
Query OK, 0 rows affected (0.30 sec)

MariaDB [vlrinst]> desc r1
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.12 sec)

MariaDB [vlrinst]> insert into r1 values(22,"ram",30);
Query OK, 1 row affected (0.07 sec)

MariaDB [vlrinst]> insert into r1 values(23,"anj",16);
Query OK, 1 row affected (0.08 sec)

MariaDB [vlrinst]> select * from r1;
+------+------+------+
| id   | name | age  |
+------+------+------+
|   22 | ram  |   30 |
|   23 | anj  |   16 |
+------+------+------+
2 rows in set (0.00 sec)

MariaDB [vlrinst]> CREATE TABLE Persons (
    ->     ID int NOT NULL,
    ->     LastName varchar(255) NOT NULL,
    ->     FirstName varchar(255),
    ->     Age int,
    ->     CHECK (Age>=18)
    -> );
Query OK, 0 rows affected (0.18 sec)

MariaDB [vlrinst]> insert into persons values(1,"chan","ram",3);
Query OK, 1 row affected (0.09 sec)

MariaDB [vlrinst]> drop table persons;
Query OK, 0 rows affected (0.23 sec)

MariaDB [vlrinst]> show tables;
+-------------------+
| Tables_in_vlrinst |
+-------------------+
| r1                |
| ram               |
| ram1              |
| ram2              |
+-------------------+
4 rows in set (0.00 sec)

MariaDB [vlrinst]> CREATE TABLE Persons (
    ->     ID int NOT NULL,
    ->     LastName varchar(255) NOT NULL,
    ->     FirstName varchar(255),
    ->     Age int,
    ->     UNIQUE (ID)
    -> );
Query OK, 0 rows affected (0.24 sec)

MariaDB [vlrinst]> insert into persons values(1,"chan","ram",3);
Query OK, 1 row affected (0.06 sec)

MariaDB [vlrinst]> insert into persons values(1,"chan","ram",3);
ERROR 1062 (23000): Duplicate entry '1' for key 'ID'
MariaDB [vlrinst]> show tables;
+-------------------+
| Tables_in_vlrinst |
+-------------------+
| persons           |
| r1                |
| ram               |
| ram1              |
| ram2              |
+-------------------+
5 rows in set (0.00 sec)

MariaDB [vlrinst]> drop table ram;
Query OK, 0 rows affected (0.16 sec)

MariaDB [vlrinst]> drop table ram1;
Query OK, 0 rows affected (0.21 sec)

MariaDB [vlrinst]> drop table ram2;
Query OK, 0 rows affected (0.16 sec)

MariaDB [vlrinst]> drop table r1;
Query OK, 0 rows affected (0.14 sec)

MariaDB [vlrinst]> drop table persons;;
Query OK, 0 rows affected (0.14 sec)

ERROR: No query specified

MariaDB [vlrinst]> show tables;
Empty set (0.00 sec)

MariaDB [vlrinst]> create table r19
    -> ;
ERROR 1113 (42000): A table must have at least 1 column
MariaDB [vlrinst]> create table r1(
    -> id int,
    -> age int,
    -> check(age>=18)
    -> );
Query OK, 0 rows affected (0.23 sec)

MariaDB [vlrinst]> create table r2(
    -> id int,
    -> name varchar(20),
    -> uinque(name)
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near '(na
me)
)' at line 4
MariaDB [vlrinst]> create table r3(
    -> id int,
    -> name varchar(25),
    -> unique(name)
    -> );
Query OK, 0 rows affected (0.25 sec)

MariaDB [vlrinst]> desc r3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(25) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

MariaDB [vlrinst]> insert into r3(id,name) values(1,"ramesh");
Query OK, 1 row affected (0.06 sec)

MariaDB [vlrinst]> insert into r3(id) values(2);
Query OK, 1 row affected (0.07 sec)

MariaDB [vlrinst]> select * from r3;
+------+--------+
| id   | name   |
+------+--------+
|    1 | ramesh |
|    2 | NULL   |
+------+--------+
2 rows in set (0.00 sec)

MariaDB [vlrinst]> insert into r3(id) values(3);
Query OK, 1 row affected (0.06 sec)

MariaDB [vlrinst]> select * from r3;
+------+--------+
| id   | name   |
+------+--------+
|    1 | ramesh |
|    2 | NULL   |
|    3 | NULL   |
+------+--------+
3 rows in set (0.00 sec)

MariaDB [vlrinst]> insert into r3(id,name) values(1,"ramesh");
ERROR 1062 (23000): Duplicate entry 'ramesh' for key 'name'
MariaDB [vlrinst]> insert into r3(id,name) values(1,"ramesh");
ERROR 1062 (23000): Duplicate entry 'ramesh' for key 'name'
MariaDB [vlrinst]> insert into r3(id) values(1);
Query OK, 1 row affected (0.04 sec)

MariaDB [vlrinst]> select * from r3;
+------+--------+
| id   | name   |
+------+--------+
|    1 | ramesh |
|    2 | NULL   |
|    3 | NULL   |
|    1 | NULL   |
+------+--------+
4 rows in set (0.00 sec)

MariaDB [vlrinst]> insert into r3(id,name) values(1,"ramesh");
ERROR 1062 (23000): Duplicate entry 'ramesh' for key 'name'
MariaDB [vlrinst]> insert into r3(id,name) values(1,"ramesh3");
Query OK, 1 row affected (0.06 sec)

MariaDB [vlrinst]>
Posted by at No comments:
Labels:

How to Specify Default Values for Columns sql table in telugu 14

 https://youtu.be/iG7UWZ-x06U

----------------------------------
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

SQL PRIMARY KEY on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:

MySQL:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

---------------
AUTO INCREMENT Field
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
CREATE TABLE Persons (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)
);
Posted by at No comments:
Labels:

SQL NOT NULL Constraint in telugu 13

 https://youtu.be/tYJ_U3JEG-o

-----------------------------------------
SQL DEFAULT Constraint
The DEFAULT constraint is used to provide a default value for a column.

The default value will be added to all new records IF no other value is specified.

SQL DEFAULT on CREATE TABLE



CREATE TABLE ram3
  (
   id INT DEFAULT 99,
    name VARCHAR(20) DEFAULT 'no name provided'
    
  );

----------
CREATE TABLE ram4
  (
   id INT  NOT NULL DEFAULT 99,
    name VARCHAR(20) NOT NULL DEFAULT 'no name provided'
    
  );


Setting environment for using XAMPP for Windows.
venkat@VENKAT-PC c:\xampp
# mysql -h localhost -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.37-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE vlrinst;
Database changed
MariaDB [vlrinst]> SHOW TABLES;
+-------------------+
| Tables_in_vlrinst |
+-------------------+
| faculty           |
| ram               |
| ram1              |
+-------------------+
3 rows in set (0.05 sec)

MariaDB [vlrinst]> desc ram; desc ram1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.06 sec)

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(25) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

MariaDB [vlrinst]> create table ram3(
    -> id int default 444,
    -> name varchar(20) default 'no name'
    -> );
Query OK, 0 rows affected (0.33 sec)

MariaDB [vlrinst]> desc ram3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | 444     |       |
| name  | varchar(20) | YES  |     | no name |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [vlrinst]> insert into ram3 values(22,'kiran');
Query OK, 1 row affected (0.10 sec)

MariaDB [vlrinst]> select * from ram3;
+------+-------+
| id   | name  |
+------+-------+
|   22 | kiran |
+------+-------+
1 row in set (0.02 sec)

MariaDB [vlrinst]> insert into ram3(id) values(22);
Query OK, 1 row affected (0.07 sec)

MariaDB [vlrinst]> select * from ram3;
+------+---------+
| id   | name    |
+------+---------+
|   22 | kiran   |
|   22 | no name |
+------+---------+
2 rows in set (0.00 sec)

MariaDB [vlrinst]> insert into ram3() values();
Query OK, 1 row affected (0.06 sec)

MariaDB [vlrinst]> select * from ram3;
+------+---------+
| id   | name    |
+------+---------+
|   22 | kiran   |
|   22 | no name |
|  444 | no name |
+------+---------+
3 rows in set (0.00 sec)

MariaDB [vlrinst]> create table ram4(
    -> id int not null default 2,
    -> name varchar not null default "no name"
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near 'not
 null default "no name"
)' at line 3
MariaDB [vlrinst]> );CREATE TABLE ram4
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near ')'
at line 1
    ->   (
    ->    id INT  NOT NULL DEFAULT 99,
    ->     name VARCHAR(20) NOT NULL DEFAULT 'no name provided'
    ->
    ->   );
Query OK, 0 rows affected (0.40 sec)

MariaDB [vlrinst]> desc ram4;
+-------+-------------+------+-----+------------------+-------+
| Field | Type        | Null | Key | Default          | Extra |
+-------+-------------+------+-----+------------------+-------+
| id    | int(11)     | NO   |     | 99               |       |
| name  | varchar(20) | NO   |     | no name provided |       |
+-------+-------------+------+-----+------------------+-------+
2 rows in set (0.01 sec)

MariaDB [vlrinst]> insert into ram4() values();
Query OK, 1 row affected (0.04 sec)

MariaDB [vlrinst]> select * from ram4;
+----+------------------+
| id | name             |
+----+------------------+
| 99 | no name provided |
+----+------------------+
1 row in set (0.00 sec)

MariaDB [vlrinst]> insert into ram4(id,name) values(3,null);
ERROR 1048 (23000): Column 'name' cannot be null
MariaDB [vlrinst]> insert into ram4(id,name) values(3);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
MariaDB [vlrinst]> insert into ram3(id,name) values(3,null);
Query OK, 1 row affected (0.06 sec)

MariaDB [vlrinst]> select * from ram3;
+------+---------+
| id   | name    |
+------+---------+
|   22 | kiran   |
|   22 | no name |
|  444 | no name |
|    3 | NULL    |
+------+---------+
4 rows in set (0.00 sec)

MariaDB [vlrinst]> select * from ram3;
+------+---------+
| id   | name    |
+------+---------+
|   22 | kiran   |
|   22 | no name |
|  444 | no name |
|    3 | NULL    |
+------+---------+
4 rows in set (0.00 sec)

MariaDB [vlrinst]>
Posted by at No comments:
Labels:

How do I show a MySQL warning that just happened in telugu 12

 https://youtu.be/60pUA4UEzPY

-----------------------------------------------------
SQL NOT NULL Constraint
By default, a column can hold NULL values.

The NOT NULL constraint enforces a column to NOT accept NULL values.

This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

SQL NOT NULL on CREATE TABLE
The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values when the "Persons" table is created:

 
Example
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);
--------------------

Setting environment for using XAMPP for Windows.
venkat@VENKAT-PC c:\xampp
# mysql -h localhost -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.37-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE vlrinst;
Database changed
MariaDB [vlrinst]> SHOW TABLES;
+-------------------+
| Tables_in_vlrinst |
+-------------------+
| faculty           |
| ram               |
+-------------------+
2 rows in set (0.06 sec)

MariaDB [vlrinst]> desc ram
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.12 sec)

MariaDB [vlrinst]> select * from ram;
+------------+--------------------------------+
| id         | name                           |
+------------+--------------------------------+
|          2 | prav                           |
|          3 | rame                           |
|        999 | dfjsfjdshfkjsdhfjksdhfkjdshfjk |
|        999 | dfjsfjdshfkjsdhfjksdhfkjdshfjk |
| 2147483647 | kiran                          |
|         22 | 45495498                       |
|          0 | u33                            |
|        123 | kiran                          |
|        123 | 876767                         |
+------------+--------------------------------+
9 rows in set (0.04 sec)

MariaDB [vlrinst]> desc ram;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [vlrinst]> insert into ram values(222);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
MariaDB [vlrinst]> insert into ram (id)values(222);
Query OK, 1 row affected (0.11 sec)

MariaDB [vlrinst]> select * from ram;
+------------+--------------------------------+
| id         | name                           |
+------------+--------------------------------+
|          2 | prav                           |
|          3 | rame                           |
|        999 | dfjsfjdshfkjsdhfjksdhfkjdshfjk |
|        999 | dfjsfjdshfkjsdhfjksdhfkjdshfjk |
| 2147483647 | kiran                          |
|         22 | 45495498                       |
|          0 | u33                            |
|        123 | kiran                          |
|        123 | 876767                         |
|        222 | NULL                           |
+------------+--------------------------------+
10 rows in set (0.00 sec)

MariaDB [vlrinst]> insert into ram (name)values("kavitha");
Query OK, 1 row affected (0.06 sec)

MariaDB [vlrinst]> select * from ram;
+------------+--------------------------------+
| id         | name                           |
+------------+--------------------------------+
|          2 | prav                           |
|          3 | rame                           |
|        999 | dfjsfjdshfkjsdhfjksdhfkjdshfjk |
|        999 | dfjsfjdshfkjsdhfjksdhfkjdshfjk |
| 2147483647 | kiran                          |
|         22 | 45495498                       |
|          0 | u33                            |
|        123 | kiran                          |
|        123 | 876767                         |
|        222 | NULL                           |
|       NULL | kavitha                        |
+------------+--------------------------------+
11 rows in set (0.00 sec)

MariaDB [vlrinst]> create table ram1(
    -> id int not null,
    -> name varchar(25) not null,
    -> age int
    -> );
Query OK, 0 rows affected (0.26 sec)

MariaDB [vlrinst]> desc ram1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(25) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

MariaDB [vlrinst]> insert into ram1(id) vlaues(333);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near 'vla
ues(333)' at line 1
MariaDB [vlrinst]> insert into ram1(id) values(333);
Query OK, 1 row affected, 1 warning (0.06 sec)

MariaDB [vlrinst]> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'name' doesn't have a default value |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [vlrinst]> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'name' doesn't have a default value |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [vlrinst]> select * from ram1;
+-----+------+------+
| id  | name | age  |
+-----+------+------+
| 333 |      | NULL |
+-----+------+------+
1 row in set (0.00 sec)

MariaDB [vlrinst]> insert into ram1(age) values(33);
Query OK, 1 row affected, 2 warnings (0.07 sec)

MariaDB [vlrinst]> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'id' doesn't have a default value   |
| Warning | 1364 | Field 'name' doesn't have a default value |
+---------+------+-------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [vlrinst]>

Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)