Translate

Friday 1 October 2021

SQL LIKE Operator sql videos in telugu 34

 https://youtu.be/y819yZjdSfA

---------------------------------------
The COUNT() function is an aggregate function that returns the number of rows in a table. The COUNT() function allows you to count all rows or only rows that match a specified condition.


The COUNT() function has three forms: COUNT(*), COUNT(expression) and COUNT(DISTINCT expression).

COUNT(*) function
The COUNT(*) function returns the number of rows in a result set returned by a SELECT statement. The COUNT(*) returns the number of rows including duplicate, non-NULL and NULL rows.

SELECT COUNT(*) FROM emp;


COUNT(expression)
The COUNT(expression) returns the number of rows that do not contain NULL values as the result of the expression.
SELECT COUNT(name) FROM emp;


COUNT(DISTINCT expression)
The COUNT(DISTINCT expression) returns the number of distinct rows that do not contain NULL values as the result of the expression.
SELECT COUNT(DISTINCT name) FROM emp;
SELECT COUNT(DISTINCT name,sal) FROM emp;


The return type of the COUNT() function is BIGINT. The COUNT()  function returns 0 if there is no matching row found.

SELECT COUNT(*) FROM emp WHERE name LIKE '%the%';
---------------

Setting environment for using XAMPP for Windows.
Mounika@MOUNIKA-PC c:\xampp
# ram.cmd
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 * from emp;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
| 10 | venkat  | kphb      |   35 | 30000 |
| 11 | praveen | pune      |   28 | 35000 |
| 12 | mounika | hyderabad |   22 | 39000 |
| 13 | revathi | jntu      |   26 | 20000 |
| 14 | anji    | gutta     |   28 | 20000 |
| 15 | harika  | lb nagar  |   26 | 30100 |
| 16 | praveen | jntu      |   33 |   100 |
| 17 | pandu   | munipeda  |   35 | 30300 |
| 18 | pandu   | pune      |   23 | 39300 |
+----+---------+-----------+------+-------+
9 rows in set (0.112 sec)

MariaDB [vlrinst]> select count(*) from emp;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.051 sec)

MariaDB [vlrinst]> select count(loc) from emp;
+------------+
| count(loc) |
+------------+
|          9 |
+------------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select count(name) from emp;
+-------------+
| count(name) |
+-------------+
|           9 |
+-------------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select count(distinct name) from emp;
+----------------------+
| count(distinct name) |
+----------------------+
|                    7 |
+----------------------+
1 row in set (0.045 sec)

MariaDB [vlrinst]> select count(distinct name,sal) from emp;
+--------------------------+
| count(distinct name,sal) |
+--------------------------+
|                        9 |
+--------------------------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select count(distinct name,loc) from emp;
+--------------------------+
| count(distinct name,loc) |
+--------------------------+
|                        9 |
+--------------------------+
1 row in set (0.001 sec)

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

MariaDB [vlrinst]> insert into emp values("pandu","pune",18,50000);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
MariaDB [vlrinst]> insert into emp values(17,"pandu","pune",18,50000);
ERROR 1062 (23000): Duplicate entry '17' for key 'PRIMARY'
MariaDB [vlrinst]> insert into emp values(27,"pandu","pune",18,50000);
Query OK, 1 row affected (0.117 sec)

MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
| 10 | venkat  | kphb      |   35 | 30000 |
| 11 | praveen | pune      |   28 | 35000 |
| 12 | mounika | hyderabad |   22 | 39000 |
| 13 | revathi | jntu      |   26 | 20000 |
| 14 | anji    | gutta     |   28 | 20000 |
| 15 | harika  | lb nagar  |   26 | 30100 |
| 16 | praveen | jntu      |   33 |   100 |
| 17 | pandu   | munipeda  |   35 | 30300 |
| 18 | pandu   | pune      |   23 | 39300 |
| 27 | pandu   | pune      |   18 | 50000 |
+----+---------+-----------+------+-------+
10 rows in set (0.001 sec)

MariaDB [vlrinst]> select count(distinct name,loc) from emp;
+--------------------------+
| count(distinct name,loc) |
+--------------------------+
|                        9 |
+--------------------------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select count(*) from emp where like "%i%";
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 'like "%i%"' at line 1
MariaDB [vlrinst]> select count(*) from emp where name like "%i%";
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select count(*) from emp where name like "kphb";
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select count(*) from emp where loc like "kphb";
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select count(*) from emp where loc = "kphb";
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.002 sec)

MariaDB [vlrinst]> select count(*) from emp where age >30;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.038 sec)

MariaDB [vlrinst]>

No comments:

Post a Comment

Note: only a member of this blog may post a comment.