Jump to content

MySQL Create and Select


Guest gagan
 Share

Recommended Posts

I was trying to solve some MySQL query questions .. Just need some feedback on the approach ..if its correct or not...1. We have a database table "txt_messages" containing an entry for each mobile text (SMS) message received by our system. Its data includes: - 10-digit US phone number of message sender - name of sender's cellular network (e.g. "cingular", "verizon") - date and time of receipt of message - first word of text message (the "keyword") a. What SQL statement(s) would you give to MySQL to create a table containing this data?create table txt_messages ( id INT(10) NOT NULL AUTO_INCREMENT, sendertel BIGINT(10) NOT NULL, network_name varchar(20), msg_recd DATETIME, keyword varchar(20), primary key (id) ); b. The first six digits of a US phone number is called the "NPA-NXX"; for example, the number "6266745901" has an NPA-NXX of "626674". Please provide a MySQL SELECT statement giving the NPA-NXX values present in the txt_messages table and the total number of messages received for each NPA-NXX between November 15, 2007 and November 30, 2007. mysql> select * from txt_messages;+----+------------+--------------+---------------------+-----------+| id | sendertel | network_name | msg_recd | keyword |+----+------------+--------------+---------------------+-----------+| 1 | 6092381579 | TMobile | 2007-12-06 08:51:06 | Hello || 2 | 6092319560 | TMobile | 2007-12-06 08:51:36 | Hello || 3 | 6191112222 | Cingular | 2007-12-06 08:53:31 | Come || 4 | 6192221111 | Cingular | 2007-12-06 08:53:50 | Go || 5 | 8180002323 | Verizon | 2007-11-15 09:08:36 | Foo || 6 | 8180004444 | Verizon | 2007-11-20 09:10:58 | Foo2 || 7 | 8180005555 | Verizon | 2007-11-20 09:11:41 | Foo23 || 8 | 8180005555 | Verizon | 2007-11-22 09:27:26 | Foo24 || 9 | 8180004444 | Verizon | 2007-11-22 09:28:09 | Foo3 |+----+------------+--------------+---------------------+-----------+9 rows in set (0.00 sec)select sendertel, SUBSTR(sendertel,1,6), COUNT(sendertel) from txt_messages where msg_recd between "2007-11-15" and "2007-11-30" GROUP BY sendertel;+------------+-----------------------+------------------+| sendertel | SUBSTR(sendertel,1,6) | COUNT(sendertel) |+------------+-----------------------+------------------+| 8180002323 | 818000 | 1 || 8180004444 | 818000 | 2 || 8180005555 | 818000 | 2 |+------------+-----------------------+------------------+ c. Enhance the SELECT statement of question b to also return the percentage of messages received between those dates with the given NPA-NXX.I need help on this query ...... I cant even understand it ..... :| :)

Link to comment
Share on other sites

The second query is grouping by the full number, not just the first 6 digits. Try this:select SUBSTR(sendertel,1,6) AS npanxx, COUNT(sendertel) from txt_messages where msg_recd between "2007-11-15" and "2007-11-30" GROUP BY npanxx;For the percentage, you can use math operators to divide the count of messages for the npanxx by the total number of messages. I don't want to write that query though, because this sounds like a school assignment. We can help you figure it out, but we try to stay away from giving the answers to assignments.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...