Question

1Write an SQL statement utilizing the WHERE, LIKE, and HAVING clauses that select gender, and the email addresses that belong to female users and have an email address that contains a number (0 to 9) within it. Create an alias for the resultant email column name and name it ‘Email Addresses Female With Numbers’

2. IPV4 (Internet Protocol Version 4) addresses utilize a notation known as the dotted-quad notation. Quad because an IPV4 address is actually a series of 4 numbers separated by a dot (hence dotted quad). Each one of the numbers has a range of 0-255. For example 255.0.65.23 is a valid IPV4 address, and so is 2.2.2.2. However, 2682.586.549.365 is NOT a valid IPV4 address (thank me later for not using IPV6 addresses for this – Google IPV6 and you will see what I mean).

   1. Write an SQL insert statement that will insert 4 rows with invalid IPV4 addresses.

   2. Write an SQL statement that will find all the rows with invalid IPV4 addresses.

      1. To do this you will need to utilize regular expressions. This is the research

         component of the lab.

      2. Regular expression documentation:

         https://dev.mysql.com/doc/refman/5.6/en/regexp.html

      3. You can look up the regular expression, you do not have to write one from scratch, but it is always a good thing to look up the syntax of regular expressions to be able to understand them.

      4. You need to validate that there are 4 numbers separated by dots each with a length of 1-3 (e.g., 999.999.999.999 is considered a valid IP address in your regular expression even though it is not in reality). Validating that there are 4 numbers separated by dots each with a length of 1-3 AND are less than 256 is a little complicated, but I encourage you to take on the challenge.

      5. By now you should see how the query from b can be created in a much cleaner fashion.

Answer 1

1.First create table profile using CREATE query

CREATE TABLE PROFILE(Name text, Gender text, Email varchar(50), IPV4 varchar(15));

2.Add values in table by using INSERT query

INSERT INTO PROFILE VALUES('Tom', 'Male', '[email protected]', '222.0.0.4');
INSERT INTO PROFILE VALUES('Tomi', 'Female', '[email protected]', '222.1.0.4');
INSERT INTO PROFILE VALUES('moni', 'Female', '[email protected]', '222.0.25.4');
INSERT INTO PROFILE VALUES('Tomu', 'Male', '[email protected]', '272.0.0.4');

3.Select values from table profile where gender is female with email containing digits using SELECT query
SELECT Gender, Email as 'Email Addresses Female With Numbers' FROM PROFILE WHERE Gender = 'Female' HAVING Email like '%[0-9]%' ;

4. Insert values in to table profile with invalid IP address

INSERT INTO PROFILE VALUES('Timi', 'Female', '[email protected]', '222.0.0.542');

INSERT INTO PROFILE VALUES('simi', 'Female', '[email protected]', '222.0.422.542');

INSERT INTO PROFILE VALUES('jimimi', 'Female', '[email protected]', '222.2222.0.542');

INSERT INTO PROFILE VALUES('aimi', 'Female', '[email protected]', '2232.0.0.542');

5. Select all rows which contains the invalid IP address using select query

select * from PROFILE where IPV4 check (ParseName(IPV4, 4) = BETWEEN 0 AND 255)
AND (ParseName(IPV4, 3) = BETWEEN 0 AND 255)
AND (ParseName(IPV4, 2) BETWEEN 0 AND 255)
AND (ParseName(IPV4, 1) BETWEEN 0 AND 255));