Laborator 5

Comparatii de stringuri, folosind LIKE si REGEXP, substring, length

##Create database - inlocuiti cu numele potrivit in cazul fiecarui student
CREATE DATABASE IF NOT EXISTS d;
use d;

#Creati tabela tStudent; in aceasta tabela veti importa dintr-un fisier datele studentilor
create table tStudent
(i int primary key,
cNume varchar(20),
cInitiala varchar(10),
cPrenume varchar(30),
cGrupa varchar(10));

Pentru a importa datele studentilor, descarcati fisierul 131IE.csv in directorul curent; in comanada de mai jos, inlocuiti calea catre fisier cu cea in care l-ati salvat:

LOAD DATA LOCAL INFILE 'C:/131IE.csv' INTO TABLE tStudent
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

alternativ, rulati urmatorul script:

INSERT INTO tStudent (i, cNume, cInitiala, cPrenume, cGrupa) VALUES
(1,'BELCIUG','M.','Adela-Carmen','131IE'),
(2,'BORDEI','Gh.','C?t?lin-Marian','131IE'),
(3,'BULDAN','I.','Alexandru','131IE'),
(4,'CALOT?','A.C.','Orlando-Petru','131IE'),
(5,'CENU?E','C.','Ana','131IE'),
(6,'CĪINARU','I.N.','Oana-Alexandra','131IE'),
(7,'CĪRSTEA','C.C.','Laura-Monica','131IE'),
(8,'DU??','A.','Andreea-Manuela','131IE'),
(9,'GAVRIL?','M.','Claudia-Oana','131IE'),
(10,'ILIE','I.','Alina-Florina','131IE'),
(11,'ISTRATE',' ','Ionu?-Lorin','131IE'),
(12,'LUPA?CU','I.','Andreea-M?d?lina','131IE'),
(13,'MARUSEC','N.','?tefan-Adrian','131IE'),
(14,'MOFLIC','I.','Marius','131IE'),
(15,'PREDA','M.','Nicoleta-Daniela','131IE'),
(16,'RADU','F.','Raisa-Floralia','131IE'),
(17,'??PURIC?','I.','Bogdan-Petric?','131IE'),
(18,'VI?AN','V.','Vasile-Cosmin','131IE')

Exercitii si raspunsuri

#Vizualizati continutul tabelei
SELECT * from tStudent;

#Selectati studentii care au nume care incep cu B
SELECT * FROM tStudent WHERE cNume LIKE 'B%';

#Selectati studentii care au nume care se termina cu a
SELECT * FROM tStudent WHERE cNume LIKE '%a';

#Selectati studentii care au nume care contin un t
SELECT * FROM tStudent WHERE cNume LIKE '%t%';

#Selectati studentii care au nume care au exact 5 caractere
SELECT * FROM tStudent WHERE cNume LIKE '_____';

#Utilizand regexp

#Selectati studentii ai caror  nume incepe cu C
SELECT * FROM tStudent WHERE cNume REGEXP '^C';

#Selectati studentii ai caror  nume incepe cu B (majuscula)
SELECT * FROM tStudent WHERE cNume REGEXP BINARY '^B';

#Selectati studentii ai caror  nume incepe cu b (minuscula)
SELECT * FROM tStudent WHERE cNume REGEXP BINARY '^b';

#Selectati studentii care au nume care se termina cu e
SELECT * FROM tStudent WHERE cNume REGEXP'e$';

#Selectati studentii care au nume care au exact 5 caractere
SELECT * FROM tStudent WHERE cNume REGEXP '^.....$';
SELECT * FROM tStudent WHERE cNume REGEXP '^.{5}$';

Exercitii suplimentare

1. Selectati studentii care au nume ce incepe cu P si prenume cu N
2. Selectati studentii ai caror nume incepe cu B si au nume din 6 litere
3. Selectati studentii cu prenumele mai lung decat numele; Hint: folositi length
4. Selectati studentii cu numele de familie care incepe cu aceeasi litera cu prenumele; Hint: folositi substring