Table: customers
| cust_id | cust_name | date_of_birth | country | created_date |
|---|---|---|---|---|
| 1 | Ram | 1995-12-25 | IN | 2022-07-17 00:00:00 |
| 2 | Ravi Krishna | 1995-06-05 | IN | 2022-07-17 08:00:00 |
| 3 | Raghav Krishna | 1995-12-25 | US | 2022-07-17 21:55:00 |
| 4 | Mark ABD | 1985-12-18 | US | 2022-07-18 06:11:05 |
| 5 | Peter David | 1995-12-25 | IN | 2022-07-18 23:05:59 |
| 6 | Alex ABD | 1989-03-22 | SG | 2022-07-20 09:10:12 |
| 7 | Surya Krishna | 1995-11-25 | US | 2022-07-20 20:35:23 |
CREATE DATABASE sqlforbi;
CREATE TABLE `customers` ( `cust_id` int NOT NULL, `cust_name` varchar(45) NOT NULL, `date_of_birth` date DEFAULT NULL, `country` varchar(45) DEFAULT NULL, `created_date` datetime DEFAULT NULL, PRIMARY KEY (`cust_id`), UNIQUE KEY `cust_id_UNIQUE` (`cust_id`) );
USE sqlforbi;INSERT INTO customers VALUES (1,"Ram","1995-12-25","IN","2022-07-17 00:00:00");
INSERT INTO customers VALUES (2,"Ravi Krishna","1995-06-05","IN","2022-07-17 08:00:00");
INSERT INTO customers VALUES (3,"Raghav Krishna","1995-12-25","US","2022-07-17 21:55:00");
INSERT INTO customers VALUES (4,"Mark ABD","1990-05-25","IN","2022-07-18 06:11:05");
INSERT INTO customers VALUES (5,"Peter David","1995-12-25","IN","2022-07-18 23:05:59");
INSERT INTO customers VALUES (6,"Alex ABD","1989-03-22","SG","2022-07-20 09:10:12");
INSERT INTO customers VALUES (7,"Surya Krishna","1995-11-25","US","2022-07-20 20:35:23");
UPDATE customers SET country="US",date_of_birth="1985-12-18" WHERE cust_id=4;
SELECT * FROM customers;
SQL query to display Customer Name and Country from customers table.
SELECT cust_name, country FROM customers ;
SQL query to display total number of customers persent in customers table.
SELECT COUNT(cust_id) FROM customers ;
How to split string value to extract first name and last name in MySQL query?
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(cust_name, ' ', 1), ' ', -1) AS "First Name",
TRIM( SUBSTR(cust_name, LOCATE(' ', cust_name)) ) AS Last_Name FROM customers;
Show employees whose names contain exactly six characters
SELECT * FROM customers WHERE cust_name LIKE '___';
Write a query to print the names whose NAME has fewer than 10 characters
SSELECT * FROM customers WHERE LENGTH(cust_name)<10;
How to find people with same last name or family name in MySQL query?
SELECT a1.cust_name, b1.cust_name FROM
(SELECT a.cust_id,a.cust_name, SUBSTRING_INDEX(SUBSTRING_INDEX(a.cust_name, ' ', 1), ' ', -1) AS "First Name",
TRIM( SUBSTR(a.cust_name, LOCATE(' ', a.cust_name)) ) AS Last_Name FROM customers a) a1,
(
SELECT a.cust_id,a.cust_name, SUBSTRING_INDEX(SUBSTRING_INDEX(a.cust_name, ' ', 1), ' ', -1) AS "First Name",
TRIM( SUBSTR(a.cust_name, LOCATE(' ', a.cust_name)) ) AS Last_Name FROM customers a) b1
WHERE a1.Last_Name = b1.Last_Name AND a1.cust_id < b1.cust_id;
Output:
| cust_name | cust_name |
| Ravi Krishna | Raghav Krishna |
| Ravi Krishna | Surya Krishna |
| Raghav Krishna | Surya Krishna |
| Mark ABD | Alex ABD |