Saturday 16 July 2011

Fairy Chat server



SOURCE CODE FOR FAIRYCHAT APPLICATION


Listing 1 is source code for FairyChatserver. The source code contains the coding to store the port on which the will listen for new connections. The chat server attempts to create a new server socket and begin to accept connections. First, a new ServerSocket object is created with a specific port. The code then enters an endless loop, continuously accepting connections and creating new chat client . The ServerSocket.accept() method waits forever until a new connection is established. When a new connection is detected, a Socket object is created inherently and returned. A new ClientConnetcion object is then constructed with the newly created socket. Since the ClientConnection is a Thread, we must call the start method to make the chat client code run.
To run the server, the users have to save the source code in java bin as “FairyChatServer.java”. Then the users have to compile in the command prompt.

LISTING 1
import java.net.*;
import java.io.*;
import java.util.*;
import java.awt.*;
import java.awt.event.*;
import java.applet.*;

public class FairyChatServer
{
// public FairyChatServer (int port) throws IOException ...
// public static void main (String args[]) throws IOException ...

public FairyChatServer () throws IOException
{
ServerSocket serverSocket = new ServerSocket (2000);
// The above statement is the ServerSocket class and its object serverSocket, which
// listen's for the port Number.

while (true)

// Here the above statement while loop which is ready to continuously listen for the
// client connection.

{
Socket client = serverSocket.accept ();

// The above statement get the client's socket.

System.out.println ("Accepted from " + client.getInetAddress ());

// Above statement get the Inet Address of each client.

ClientsConnections cCon = new ClientsConnections (client);
cCon.start ();
}
}


public static void main (String args[]) throws Exception
{
new FairyChatServer ();
}

}

class ClientsConnections extends Thread
{
protected Socket socket;
protected DataInputStream dti;
protected DataOutputStream dto;
static String selFont,selStyle,sendStr;
static int selSize;

public ClientsConnections (Socket s) throws IOException
{
this.socket = s;
dti = new DataInputStream (new BufferedInputStream (s.getInputStream ()));
dto = new DataOutputStream (new BufferedOutputStream (s.getOutputStream ()));
}

protected static Vector clientConnections = new Vector ();

public void run ()
{
try {
clientConnections.addElement (this);
while (true)
{
String msg = dti.readUTF ();
/* int i,li,starti,startli,end,chs;
String sub,sub1;
i=msg.indexOf(">");
starti=i+1;
li=msg.lastIndexOf(">");
startli=li+1;
sub=msg.substring(starti);
int subi=sub.length();
int start=0;
end=subi-3;
char ch[]=new char[end-start];
sub.getChars(start,end,ch,0);
sub1=msg.substring(startli);
String getCh=new String(ch);
selFont=new String(getCh);
int geNum=Integer.parseInt(sub1);
selSize=geNum;
int st,endStr;
st=0;
endStr=i;
char chrs[]=new char[endStr-st];
msg.getChars(st,endStr,chrs,0);
String getChs=new String(chrs);
sendStr=new String(getChs);*/
broadcast (msg);
}
} catch (IOException ex)
{
System.out.println(socket.getInetAddress()+" Client has been closed Connection...");
} finally
{
clientConnections.removeElement (this);
try
{
socket.close ();
} catch (IOException ex)
{
ex.printStackTrace();
}
}
}

// protected static void broadcast (String message) ...

protected static void broadcast (String message)
{
synchronized (clientConnections)
{
Enumeration e = clientConnections.elements ();
while (e.hasMoreElements ())
{
ClientsConnections cTypecast = (ClientsConnections) e.nextElement ();
try
{
synchronized (cTypecast.dto)
{
cTypecast.dto.writeUTF (message);
}
cTypecast.dto.flush ();
} catch (IOException ex)
{
cTypecast.stop ();
}
}
}
}
}

Listing 2 is source code to open the chat window for client to chat. The source code contains layout of Login window to log into the chat application such as user name, user password, start button and cancel button in UserFrame( ). Then next part of source code part contains the layout of the chat applet with certain main function to run the application. To run the chat applet the user have to save the source code in java bin as ClientWindow.java. Then user have to compile in command prompt in order to open the chat window applet and start chatting.

LISTING 2
import java.io.*;
import java.net.*;
import java.util.*;
import java.awt.*;
import java.awt.event.*;


public class ClientWindow
{
public static void main(String args[])
{
Frame userFrame=new UserFrame();
userFrame.setTitle("Login Chat !");
userFrame.setSize(150,250);
userFrame.show();
}
}

class UserFrame extends Frame implements ActionListener
{
Frame chatClient;
Label nameLabel,passLabel,serverLabel;
TextField userText,passText,serverText;
Button startButton,cancelButton;

UserFrame()
{
setLayout(new FlowLayout(FlowLayout.LEFT));

nameLabel=new Label("User Name : ");
passLabel=new Label("Password : ");
serverLabel=new Label("Server Name : ");
userText=new TextField(15);
passText=new TextField(15);
passText.setEchoChar('*');
serverText=new TextField(15);
startButton=new Button("Start");
cancelButton=new Button("Cancel");
add(nameLabel);add(userText);

add(passLabel);add(passText);
add(serverLabel);add(serverText);
add(startButton);add(cancelButton);

addWindowListener(new WindowAdapter()
{
public void windowClosing(WindowEvent e)
{
System.exit(0);
}
});

startButton.addActionListener(this);
}
public void actionPerformed(ActionEvent e)
{
try
{
String uName,sName;
uName=userText.getText();
sName=serverText.getText();
Socket socket=new Socket(sName,2000);
chatClient=new

ChatClient1(socket,uName,sName,socket.getInputStream(),socket.getOutputStream());
chatClient.setTitle(uName+" : ChatWindow!");
chatClient.setSize(400,270);
chatClient.show();

}catch(Exception exp)
{}
}
}
class ChatClient1 extends Frame implements Runnable
{
protected DataInputStream dti;
protected DataOutputStream dto;
Socket socket;
protected TextArea dispMessage;
protected TextField sendMessage;
protected TextField input,uName;
protected Button sendButton;
protected Label selectColor,selectFont;
protected Choice choiceColor,choiceFont,choiceSize;
String userName;
String s;
String selFont,selStyle;
int selSize;
protected Thread listener;

public ChatClient1 (Socket socket,String userName,String serveName,InputStream

is,OutputStream os)
{
this.userName=userName;
dti=new DataInputStream(new BufferedInputStream(is));
dto=new DataOutputStream(new BufferedOutputStream(os));

setLayout (new FlowLayout(FlowLayout.LEFT));
setBackground(Color.blue);
setForeground(Color.black);
dispMessage=new TextArea(8,50);
selectColor=new Label("Colors ");
selectFont=new Label(" Font ");
choiceColor=new Choice();
choiceFont=new Choice();
choiceSize=new Choice();

GraphicsEnvironment ge=GraphicsEnvironment.getLocalGraphicsEnvironment();
String[] fontNames=ge.getAvailableFontFamilyNames();
for(int i=0;i<fontNames.length;i++)
choiceFont.add(fontNames[i]);
String sizeFont;
for(int i=8;i<=100;i++)
{
sizeFont=Integer.toString(i);
choiceSize.add(sizeFont);
}

uName=new TextField(userName,15);
sendMessage=new TextField(45);
sendButton=new Button("Send");

add(uName);
add(dispMessage);
add(selectColor);add(choiceColor);
add(selectFont);add(choiceFont);add(choiceSize);
add(sendMessage);add(sendButton);
sendMessage.requestFocus ();
uName.setEditable(false);
dispMessage.setEditable(false);
listener=new Thread(this);
listener.start();

addWindowListener(new WindowAdapter()
{
public void windowClosing(WindowEvent we)
{
System.exit(0);
}
});


}



public void run ()
{
try {
while (true)
{
String line = dti.readUTF ();
dispMessage.appendText (line + "\n");
}
} catch (IOException ex)
{
ex.printStackTrace ();
} finally
{
listener = null;
sendMessage.hide ();
validate ();
try
{
dto.close ();
} catch (IOException ex)
{
ex.printStackTrace ();
}
}
}
public boolean handleEvent (Event e)
{
if ((e.target == sendMessage) && (e.id == Event.ACTION_EVENT))
{
try
{
dto.writeUTF (userName+" : "+(String) e.arg);
dto.flush ();
} catch (IOException ex)
{
ex.printStackTrace();
listener.stop ();
}
sendMessage.setText ("");
return true;
}
else if ((e.target == sendButton) && (e.id == Event.ACTION_EVENT))
{
try
{
s=userName+" : "+sendMessage.getText();
dto.writeUTF (s);
dto.flush ();
} catch (IOException ex)
{
ex.printStackTrace();
listener.stop ();
}
sendMessage.setText ("");
return true;

} else if ((e.target == this) && (e.id == Event.WINDOW_DESTROY))
{
if (listener != null)
listener.stop ();
hide ();
return true;
}
else if((e.target == choiceSize) && (e.id == Event.ACTION_EVENT))
{
String selsStr;
selsStr=choiceSize.getSelectedItem();
selSize=Integer.parseInt(selsStr);
return true;
}
else if((e.target == choiceFont) && (e.id == Event.ACTION_EVENT))
{
selFont=choiceFont.getSelectedItem();
return true;
}

return super.handleEvent (e);
}
}



USER MANUAL


To Use system


System Requirements

Operating System: Windows* 98/Me/2000/XP
Processor: Pentium* III 800 MHz or any affordable processor
Memory: 128 MB RAM (256 MB RAM Recommended)
Software: 1) j2sdk1.4.2_15 (77.3 MB size)
2) Command prompt
3) Notepad
Misc. Hardware: Keyboard and mouse (compatible with PC or laptop)



INSTRUCTION


  1. Start Windows* 98/Me/2000/XP.
  2. Copy FairyChatServer and ClientWindow source code in notepad and save it in java bin according to the class name .
  3. Open command prompt and compile FairyChatServer.java
  4. Then Open a different command prompt an compile ClientWindow.java.
  5. Once the Chat window open, user may start to chat.

Step 1:
Compile FairyChatServer.java in Command prompt










Step 2:
Compile ClientWindow.java in Command prompt















Step 3:
Key in User name ,Password and server name in login Applet once it appear after compiling ClientWindow.java


















Step 4:
Click the Start button to log in.

Step 5:
User may start chat once the Chatwindow appear.





















Step 6:
To send the text .User may enter the text in text box and click send to send the text to other user and wait for the reply from other user.




















Step 7:
To exit the Chat room, user has to click the close button to exit


so guys who want to have their own chat server in college or among freinds. Can use this chat applications.

Database - mysql queries -part12

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database Customer;
Query OK, 1 row affected (0.00 sec)

mysql> use Customer;
Database changed
mysql> create table Customer
    -> (Cust_Num char(3) primary key,
    -> Cust_Name char(20),
    -> Street char(25),
    -> City char(15),
    -> Balance decimal(8,2),
    -> Credit_Limit decimal(8,2),
    -> Rep_Num char(3));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into Customer values
    -> ('148','Hot Sports Center','2115 Jln Mansoor','Kangar','6550.00','7500.00
','20');
Query OK, 1 row affected (0.06 sec)

mysql> insert into Customer values
    -> ('282','Panas Direct','3827 Nawawi','Kuching','431.540','10000.00','35');

Query OK, 1 row affected (0.05 sec)

mysql> insert into Customer values
    -> ('356','Fergusons','482 WildWest','Delhi','5785.00','7500.00','65');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Customer values
    -> ('408','Everyone Corner','1827 Fojia','Tunis','5285.00','5000.00','35');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Customer values
    -> ('462','Bergains Galore','3829 Central','Klang','3412.00','10000.00','65'
);
Query OK, 1 row affected (0.03 sec)

mysql> insert into Customer values
    -> ('524','klines','838 Ridge Central','Tripoli','12762.00','15000.00','20')
;
Query OK, 1 row affected (0.03 sec)

mysql> insert into Customer values
    -> ('608','Uncle Don','321 Nizra','Kuching','2100.00','10000.00','65');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Customer values
    -> ('687','Lees Conner','213 West','Penang','2851.00','5000.00','35');
Query OK, 1 row affected (0.05 sec)

mysql> select*
    -> from Customer;
+----------+-------------------+-------------------+---------+----------+-------
-------+---------+
| Cust_Num | Cust_Name         | Street            | City    | Balance  | Credit
_Limit | Rep_Num |
+----------+-------------------+-------------------+---------+----------+-------
-------+---------+
| 148      | Hot Sports Center | 2115 Jln Mansoor  | Kangar  |  6550.00 |      7
500.00 | 20      |
| 282      | Panas Direct      | 3827 Nawawi       | Kuching |   431.54 |     10
000.00 | 35      |
| 356      | Fergusons         | 482 WildWest      | Delhi   |  5785.00 |      7
500.00 | 65      |
| 408      | Everyone Corner   | 1827 Fojia        | Tunis   |  5285.00 |      5
000.00 | 35      |
| 462      | Bergains Galore   | 3829 Central      | Klang   |  3412.00 |     10
000.00 | 65      |
| 524      | klines            | 838 Ridge Central | Tripoli | 12762.00 |     15
000.00 | 20      |
| 608      | Uncle Don         | 321 Nizra         | Kuching |  2100.00 |     10
000.00 | 65      |
| 687      | Lees Conner       | 213 West          | Penang  |  2851.00 |      5
000.00 | 35      |
+----------+-------------------+-------------------+---------+----------+-------
-------+---------+
8 rows in set (0.00 sec)


mysql> create table Orders
    -> (Order_Num char(5) primary key,
    -> Order_Date DATE,
    -> Cust_Num char(3));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into Orders values
    -> ('21608','2008-09-20','148');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Orders values
    -> ('21610','2008-09-20','356');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Orders values
    -> ('21613','2008-09-21','408');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Orders values
    -> ('21614','2008-09-21','282');
Query OK, 1 row affected (0.02 sec)

mysql> insert into Orders values
    -> ('21617','2008-09-23','608');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Orders values
    -> ('21619','2008-09-23','148');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Orders values
    -> ('21623','2008-09-23','608');
Query OK, 1 row affected (0.03 sec)

mysql> select*
    -> from Orders;
+-----------+------------+----------+
| Order_Num | Order_Date | Cust_Num |
+-----------+------------+----------+
| 21608     | 2008-09-20 | 148      |
| 21610     | 2008-09-20 | 356      |
| 21613     | 2008-09-21 | 408      |
| 21614     | 2008-09-21 | 282      |
| 21617     | 2008-09-23 | 608      |
| 21619     | 2008-09-23 | 148      |
| 21623     | 2008-09-23 | 608      |
+-----------+------------+----------+
7 rows in set (0.00 sec)

mysql> select C.Cust_Num, O.Order_num, Order_Date from Customer C, Orders O wher
e C.Cust_Num=O.Cust_Num;
+----------+-----------+------------+
| Cust_Num | Order_num | Order_Date |
+----------+-----------+------------+
| 148      | 21608     | 2008-09-20 |
| 356      | 21610     | 2008-09-20 |
| 408      | 21613     | 2008-09-21 |
| 282      | 21614     | 2008-09-21 |
| 608      | 21617     | 2008-09-23 |
| 148      | 21619     | 2008-09-23 |
| 608      | 21623     | 2008-09-23 |
+----------+-----------+------------+
7 rows in set (0.00 sec)

mysql> select C.Cust_Num,Cust_Name,O.Order_Num,Order_Date from Customer C,Orders
 O where C.Cust_Num=O.Cust_Num;
+----------+-------------------+-----------+------------+
| Cust_Num | Cust_Name         | Order_Num | Order_Date |
+----------+-------------------+-----------+------------+
| 148      | Hot Sports Center | 21608     | 2008-09-20 |
| 356      | Fergusons         | 21610     | 2008-09-20 |
| 408      | Everyone Corner   | 21613     | 2008-09-21 |
| 282      | Panas Direct      | 21614     | 2008-09-21 |
| 608      | Uncle Don         | 21617     | 2008-09-23 |
| 148      | Hot Sports Center | 21619     | 2008-09-23 |
| 608      | Uncle Don         | 21623     | 2008-09-23 |
+----------+-------------------+-----------+------------+
7 rows in set (0.00 sec)

mysql> select Customer.Cust_Num,Cust_Name,Order_Num,Order_Date from Customer INN
ER JOIN ORDERS ON Customer.Cust_Num=Orders.Cust_Num ORDER BY Customer.Cust_Num;
+----------+-------------------+-----------+------------+
| Cust_Num | Cust_Name         | Order_Num | Order_Date |
+----------+-------------------+-----------+------------+
| 148      | Hot Sports Center | 21608     | 2008-09-20 |
| 148      | Hot Sports Center | 21619     | 2008-09-23 |
| 282      | Panas Direct      | 21614     | 2008-09-21 |
| 356      | Fergusons         | 21610     | 2008-09-20 |
| 408      | Everyone Corner   | 21613     | 2008-09-21 |
| 608      | Uncle Don         | 21623     | 2008-09-23 |
| 608      | Uncle Don         | 21617     | 2008-09-23 |
+----------+-------------------+-----------+------------+
7 rows in set (0.03 sec)

mysql> select Customer.Cust_Num,Cust_Name,Order_Num,Order_Date from Customer LEF
T JOIN ORDERS ON Customer.Cust_Num=Orders.Cust_Num ORDER BY Customer.Cust_Num;
+----------+-------------------+-----------+------------+
| Cust_Num | Cust_Name         | Order_Num | Order_Date |
+----------+-------------------+-----------+------------+
| 148      | Hot Sports Center | 21608     | 2008-09-20 |
| 148      | Hot Sports Center | 21619     | 2008-09-23 |
| 282      | Panas Direct      | 21614     | 2008-09-21 |
| 356      | Fergusons         | 21610     | 2008-09-20 |
| 408      | Everyone Corner   | 21613     | 2008-09-21 |
| 462      | Bergains Galore   | NULL      | NULL       |
| 524      | klines            | NULL      | NULL       |
| 608      | Uncle Don         | 21617     | 2008-09-23 |
| 608      | Uncle Don         | 21623     | 2008-09-23 |
| 687      | Lees Conner       | NULL      | NULL       |
+----------+-------------------+-----------+------------+
10 rows in set (0.01 sec)

mysql> select Customer.Cust_Num,Cust_Name,Order_Num,Order_Date from Customer,Ord
ers;
+----------+-------------------+-----------+------------+
| Cust_Num | Cust_Name         | Order_Num | Order_Date |
+----------+-------------------+-----------+------------+
| 148      | Hot Sports Center | 21608     | 2008-09-20 |
| 148      | Hot Sports Center | 21610     | 2008-09-20 |
| 148      | Hot Sports Center | 21613     | 2008-09-21 |
| 148      | Hot Sports Center | 21614     | 2008-09-21 |
| 148      | Hot Sports Center | 21617     | 2008-09-23 |
| 148      | Hot Sports Center | 21619     | 2008-09-23 |
| 148      | Hot Sports Center | 21623     | 2008-09-23 |
| 282      | Panas Direct      | 21608     | 2008-09-20 |
| 282      | Panas Direct      | 21610     | 2008-09-20 |
| 282      | Panas Direct      | 21613     | 2008-09-21 |
| 282      | Panas Direct      | 21614     | 2008-09-21 |
| 282      | Panas Direct      | 21617     | 2008-09-23 |
| 282      | Panas Direct      | 21619     | 2008-09-23 |
| 282      | Panas Direct      | 21623     | 2008-09-23 |
| 356      | Fergusons         | 21608     | 2008-09-20 |
| 356      | Fergusons         | 21610     | 2008-09-20 |
| 356      | Fergusons         | 21613     | 2008-09-21 |
| 356      | Fergusons         | 21614     | 2008-09-21 |
| 356      | Fergusons         | 21617     | 2008-09-23 |
| 356      | Fergusons         | 21619     | 2008-09-23 |
| 356      | Fergusons         | 21623     | 2008-09-23 |
| 408      | Everyone Corner   | 21608     | 2008-09-20 |
| 408      | Everyone Corner   | 21610     | 2008-09-20 |
| 408      | Everyone Corner   | 21613     | 2008-09-21 |
| 408      | Everyone Corner   | 21614     | 2008-09-21 |
| 408      | Everyone Corner   | 21617     | 2008-09-23 |
| 408      | Everyone Corner   | 21619     | 2008-09-23 |
| 408      | Everyone Corner   | 21623     | 2008-09-23 |
| 462      | Bergains Galore   | 21608     | 2008-09-20 |
| 462      | Bergains Galore   | 21610     | 2008-09-20 |
| 462      | Bergains Galore   | 21613     | 2008-09-21 |
| 462      | Bergains Galore   | 21614     | 2008-09-21 |
| 462      | Bergains Galore   | 21617     | 2008-09-23 |
| 462      | Bergains Galore   | 21619     | 2008-09-23 |
| 462      | Bergains Galore   | 21623     | 2008-09-23 |
| 524      | klines            | 21608     | 2008-09-20 |
| 524      | klines            | 21610     | 2008-09-20 |
| 524      | klines            | 21613     | 2008-09-21 |
| 524      | klines            | 21614     | 2008-09-21 |
| 524      | klines            | 21617     | 2008-09-23 |
| 524      | klines            | 21619     | 2008-09-23 |
| 524      | klines            | 21623     | 2008-09-23 |
| 608      | Uncle Don         | 21608     | 2008-09-20 |
| 608      | Uncle Don         | 21610     | 2008-09-20 |
| 608      | Uncle Don         | 21613     | 2008-09-21 |
| 608      | Uncle Don         | 21614     | 2008-09-21 |
| 608      | Uncle Don         | 21617     | 2008-09-23 |
| 608      | Uncle Don         | 21619     | 2008-09-23 |
| 608      | Uncle Don         | 21623     | 2008-09-23 |
| 687      | Lees Conner       | 21608     | 2008-09-20 |
| 687      | Lees Conner       | 21610     | 2008-09-20 |
| 687      | Lees Conner       | 21613     | 2008-09-21 |
| 687      | Lees Conner       | 21614     | 2008-09-21 |
| 687      | Lees Conner       | 21617     | 2008-09-23 |
| 687      | Lees Conner       | 21619     | 2008-09-23 |
| 687      | Lees Conner       | 21623     | 2008-09-23 |
+----------+-------------------+-----------+------------+
56 rows in set (0.06 sec)

mysql> select Customer.Cust_Num,Cust_Name,Order_Num,Order_Date from Customer,Ord
ers ORDER BY Customer.Cust_Num;
+----------+-------------------+-----------+------------+
| Cust_Num | Cust_Name         | Order_Num | Order_Date |
+----------+-------------------+-----------+------------+
| 148      | Hot Sports Center | 21608     | 2008-09-20 |
| 148      | Hot Sports Center | 21610     | 2008-09-20 |
| 148      | Hot Sports Center | 21613     | 2008-09-21 |
| 148      | Hot Sports Center | 21614     | 2008-09-21 |
| 148      | Hot Sports Center | 21617     | 2008-09-23 |
| 148      | Hot Sports Center | 21619     | 2008-09-23 |
| 148      | Hot Sports Center | 21623     | 2008-09-23 |
| 282      | Panas Direct      | 21608     | 2008-09-20 |
| 282      | Panas Direct      | 21610     | 2008-09-20 |
| 282      | Panas Direct      | 21613     | 2008-09-21 |
| 282      | Panas Direct      | 21614     | 2008-09-21 |
| 282      | Panas Direct      | 21617     | 2008-09-23 |
| 282      | Panas Direct      | 21619     | 2008-09-23 |
| 282      | Panas Direct      | 21623     | 2008-09-23 |
| 356      | Fergusons         | 21623     | 2008-09-23 |
| 356      | Fergusons         | 21608     | 2008-09-20 |
| 356      | Fergusons         | 21610     | 2008-09-20 |
| 356      | Fergusons         | 21613     | 2008-09-21 |
| 356      | Fergusons         | 21614     | 2008-09-21 |
| 356      | Fergusons         | 21617     | 2008-09-23 |
| 356      | Fergusons         | 21619     | 2008-09-23 |
| 408      | Everyone Corner   | 21619     | 2008-09-23 |
| 408      | Everyone Corner   | 21623     | 2008-09-23 |
| 408      | Everyone Corner   | 21608     | 2008-09-20 |
| 408      | Everyone Corner   | 21610     | 2008-09-20 |
| 408      | Everyone Corner   | 21613     | 2008-09-21 |
| 408      | Everyone Corner   | 21614     | 2008-09-21 |
| 408      | Everyone Corner   | 21617     | 2008-09-23 |
| 462      | Bergains Galore   | 21617     | 2008-09-23 |
| 462      | Bergains Galore   | 21619     | 2008-09-23 |
| 462      | Bergains Galore   | 21623     | 2008-09-23 |
| 462      | Bergains Galore   | 21608     | 2008-09-20 |
| 462      | Bergains Galore   | 21610     | 2008-09-20 |
| 462      | Bergains Galore   | 21613     | 2008-09-21 |
| 462      | Bergains Galore   | 21614     | 2008-09-21 |
| 524      | klines            | 21614     | 2008-09-21 |
| 524      | klines            | 21617     | 2008-09-23 |
| 524      | klines            | 21619     | 2008-09-23 |
| 524      | klines            | 21623     | 2008-09-23 |
| 524      | klines            | 21608     | 2008-09-20 |
| 524      | klines            | 21610     | 2008-09-20 |
| 524      | klines            | 21613     | 2008-09-21 |
| 608      | Uncle Don         | 21610     | 2008-09-20 |
| 608      | Uncle Don         | 21613     | 2008-09-21 |
| 608      | Uncle Don         | 21614     | 2008-09-21 |
| 608      | Uncle Don         | 21617     | 2008-09-23 |
| 608      | Uncle Don         | 21619     | 2008-09-23 |
| 608      | Uncle Don         | 21623     | 2008-09-23 |
| 608      | Uncle Don         | 21608     | 2008-09-20 |
| 687      | Lees Conner       | 21608     | 2008-09-20 |
| 687      | Lees Conner       | 21610     | 2008-09-20 |
| 687      | Lees Conner       | 21613     | 2008-09-21 |
| 687      | Lees Conner       | 21614     | 2008-09-21 |
| 687      | Lees Conner       | 21617     | 2008-09-23 |
| 687      | Lees Conner       | 21619     | 2008-09-23 |
| 687      | Lees Conner       | 21623     | 2008-09-23 |
+----------+-------------------+-----------+------------+
56 rows in set (0.01 sec)

Database - mysql queries -part11

Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database custRepsystem;
Query OK, 1 row affected (0.03 sec)

mysql> use custRepsystem;
Database changed

mysql> create table customer (cust_num char(8) primary key,cust_name char(20) no
t null,street char(20),city char(20),balance decimal(8,2)not null ,credit_limit
decimal(8,2) not null ,rep_num char(3));
Query OK, 0 rows affected (0.17 sec)

mysql> create table rep(rep_num char(3) primary key not null,
not  null,first_name char(20) not null,street char(20),city char(20));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into customer
    -> values('148','hot sports center','2115 jln mansoor','kangar','6555.00','7
500.00','20');
Query OK, 1 row affected (0.14 sec)

mysql> insert into customer
    -> values('282','panas direct','3827 nawawi','kuching','4315.40','10000.00',
'35');
Query OK, 1 row affected (0.02 sec)

mysql> insert into customer
    -> values('356','fergusons','482 wildwest','delhi','5785.00','7500.00','65'
);
Query OK, 1 row affected (0.03 sec)

mysql> insert into customer
    -> values('409','everyone corner','1827 fojia','tunis','5285.00','5000.00','
35');
Query OK, 1 row affected (0.03 sec)

mysql> insert into customer
    -> values('462','bargains galore','3829 central','klang','3412.00','10000.00
','65');
Query OK, 1 row affected (0.01 sec)

mysql> insert into customer
    -> values('524','klines','838 ridge','tripoli','12762.00','15000.00','20');
Query OK, 1 row affected (0.01 sec)

mysql> insert into customer
    -> values('608','uncle don','321 nizra','kuching','2100.00','10000.00','65')
;
Query OK, 1 row affected (0.03 sec)

mysql> insert into customer
    -> values('687','lees corner','213 west','penang','2851.00','5000.00','35');

Query OK, 1 row affected (0.03 sec)

mysql> select * from customer;
+----------+-------------------+------------------+---------+----------+--------
------+---------+
| cust_num | cust_name         | street           | city    | balance  | credit_
limit | rep_num |
+----------+-------------------+------------------+---------+----------+--------
------+---------+
| 148      | hot sports center | 2115 jln mansoor | kangar  |  6555.00 |      75
00.00 | 20      |
| 282      | panas direct      | 3827 nawawi      | kuching |  4315.40 |     100
00.00 | 35      |
| 356      | fergusons         | 482 wildwest     | delhi   |  5785.00 |     75
00.00 | 65      |
| 409      | everyone corner   | 1827 fojia       | tunis   |  5285.00 |      50
00.00 | 35      |
| 462      | bargains galore   | 3829 central     | klang   |  3412.00 |     100
00.00 | 65      |
| 524      | klines            | 838 ridge        | tripoli | 12762.00 |     150
00.00 | 20      |
| 608      | uncle don         | 321 nizra        | kuching |  2100.00 |     100
00.00 | 65      |
| 687      | lees corner       | 213 west         | penang  |  2851.00 |      50
00.00 | 35      |
+----------+-------------------+------------------+---------+----------+--------
------+---------+
8 rows in set (0.06 sec)


mysql> insert into rep
    -> values('20','mohd','zambri','621 telawi','kuantan');
Query OK, 1 row affected (0.05 sec)

mysql> insert into rep
    -> values('35','fuad','razi','532 mansoor','kangar');
Query OK, 1 row affected (0.02 sec)

mysql> insert into rep
    -> values('65','perez','ali','1625 taylor','johor');
Query OK, 1 row affected (0.02 sec)

mysql> select * from rep;
+---------+-----------+------------+-------------+---------+
| rep_num | last_name | first_name | street      | city    |
+---------+-----------+------------+-------------+---------+
| 20      | mohd      | zambri     | 621 telawi  | kuantan |
| 35      | fuad      | razi       | 532 mansoor | kangar  |
| 65      | perez     | ali        | 1625 taylor | johor   |
+---------+-----------+------------+-------------+---------+
3 rows in set (0.00 sec)

mysql> select cust_num,cust_name,first_name,rep.rep_num from customer,rep where
(customer.rep_num=rep.rep_num);
+----------+-------------------+------------+---------+
| cust_num | cust_name         | first_name | rep_num |
+----------+-------------------+------------+---------+
| 148      | hot sports center | zambri     | 20      |
| 282      | panas direct      | razi       | 35      |
| 356      | fergusons         | ali        | 65      |
| 409      | everyone corner   | razi       | 35      |
| 462      | bargains galore   | ali        | 65      |
| 524      | klines            | zambri     | 20      |
| 608      | uncle don         | ali        | 65      |
| 687      | lees corner       | razi       | 35      |
+----------+-------------------+------------+---------+
8 rows in set (0.03 sec)

mysql> select cust_num,cust_name,balance,first_name,rep.rep_num from customer,re
p where (customer.rep_num=rep.rep_num);
+----------+-------------------+----------+------------+---------+
| cust_num | cust_name         | balance  | first_name | rep_num |
+----------+-------------------+----------+------------+---------+
| 148      | hot sports center |  6555.00 | zambri     | 20      |
| 282      | panas direct      |  4315.40 | razi       | 35      |
| 356      | fergusons         |  5785.00 | ali        | 65      |
| 409      | everyone corner   |  5285.00 | razi       | 35      |
| 462      | bargains galore   |  3412.00 | ali        | 65      |
| 524      | klines            | 12762.00 | zambri     | 20      |
| 608      | uncle don         |  2100.00 | ali        | 65      |
| 687      | lees corner       |  2851.00 | razi       | 35      |
+----------+-------------------+----------+------------+---------+
8 rows in set (0.00 sec)




mysql> select cust_num,cust_name,credit_limit,first_name,rep.rep_num,rep.city fr
om customer,rep where (customer.rep_num=rep.rep_num);
+----------+-------------------+--------------+------------+---------+---------+

| cust_num | cust_name         | credit_limit | first_name | rep_num | city    |

+----------+-------------------+--------------+------------+---------+---------+

| 148      | hot sports center |      7500.00 | zambri     | 20      | kuantan |

| 282      | panas direct      |     10000.00 | razi       | 35      | kangar  |

| 356      | fergusons         |     75000.00 | ali        | 65      | johor   |

| 409      | everyone corner   |      5000.00 | razi       | 35      | kangar  |

| 462      | bargains galore   |     10000.00 | ali        | 65      | johor   |

| 524      | klines            |     15000.00 | zambri     | 20      | kuantan |

| 608      | uncle don         |     10000.00 | ali        | 65      | johor   |

| 687      | lees corner       |      5000.00 | razi       | 35      | kangar  |

+----------+-------------------+--------------+------------+---------+---------+

8 rows in set (0.00 sec)


mysql> select cust_num,cust_name,first_name,rep.rep_num from customer,rep where
customer.rep_num=rep.rep_num AND credit_limit='7500.00';
+----------+-------------------+------------+---------+
| cust_num | cust_name         | first_name | rep_num |
+----------+-------------------+------------+---------+
| 148      | hot sports center | zambri     | 20      |
| 356      | fergusons         | ali        | 65      |
+----------+-------------------+------------+---------+
2 rows in set (0.00 sec)

mysql>

Database - mysql queries -part10

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database naruto;
Query OK, 1 row affected (0.00 sec)

mysql> use naruto;
Database changed
mysql> create table Family
    -> (Fam_Num char(3) primary key,
    -> Fam_Name char(20),
    -> Street char(20),
    -> Zip_Code decimal(6),
    -> City char(20),
    -> State char(15),
    -> ID_No char(15));
Query OK, 0 rows affected (0.20 sec)

mysql> insert into Family values
    -> ('100','Adawiah','Kg Pasir Era','18000','Kuala Krai','Kelantan','A 501');

Query OK, 1 row affected (0.08 sec)

mysql> insert into Family values
    -> ('101','Hadiyatullah','Kg Enggong','18000','Kuala Krai','Kelantan','B 502
');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Family values
    -> ('102','Sharifuddin','Kg Enggong','18000','Kuala Krai','Kelantan','C 503'
);
Query OK, 1 row affected (0.03 sec)

mysql> insert into Family values
    -> ('103','Iklima','Jln Pahlawan','52000','Damansara','Selangor','D 504');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Family values
    -> ('104','Malikah','Bdr Sulaiman','53300','Port Klang','Selangor','E 505');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Family values
    -> ('105','Nik Hassan','Bdr Sulaiman','53300','Port Klang','Selangor','F 506
');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Family values
    -> ('106','Ahmad','Jln Kenari','52300','Wangsa Maju','Kuala Lumpur','G 507')
;
Query OK, 1 row affected (0.03 sec)

mysql> select*
    -> from Family;
+---------+--------------+--------------+----------+-------------+--------------
+-------+
| Fam_Num | Fam_Name     | Street       | Zip_Code | City        | State
| ID_No |
+---------+--------------+--------------+----------+-------------+--------------
+-------+
| 100     | Adawiah      | Kg Pasir Era |    18000 | Kuala Krai  | Kelantan
| A 501 |
| 101     | Hadiyatullah | Kg Enggong   |    18000 | Kuala Krai  | Kelantan
| B 502 |
| 102     | Sharifuddin  | Kg Enggong   |    18000 | Kuala Krai  | Kelantan
| C 503 |
| 103     | Iklima       | Jln Pahlawan |    52000 | Damansara   | Selangor
| D 504 |
| 104     | Malikah      | Bdr Sulaiman |    53300 | Port Klang  | Selangor
| E 505 |
| 105     | Nik Hassan   | Bdr Sulaiman |    53300 | Port Klang  | Selangor
| F 506 |
| 106     | Ahmad        | Jln Kenari   |    52300 | Wangsa Maju | Kuala Lumpur
| G 507 |
+---------+--------------+--------------+----------+-------------+--------------
+-------+
7 rows in set (0.02 sec)

mysql> create table Owner
    -> (ID_No char(15) primary key,
    -> Type char(15),
    -> Model_Car char(15),
    -> PymtDate Date,
    -> PymtMade decimal(8,2),
    -> PymtPrice decimal(8,2),
    -> PymtType char(10));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into Owner values
    -> ('A 501','PERODUA','MYVI','1999/05/05','450.00','600.00','Cash');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('B 502','PROTON','PERDANA','1999/10/10','450.00','500.00','Cash');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Owner values
    -> ('C 503','PROTON','SAGA','2000/10/10','550.00','550.00','Instalment');
Query OK, 1 row affected (0.11 sec)

mysql> insert into Owner values
    -> ('D 504','PERODUA','KELISA','2001/10/15','550.00','650.00','Cash');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('E 505','PERODUA','KENARI','2003/05/25','650.00','750.00','Cash');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('F 506','MITSUBSHI','LANCER','2004/05/25','450.00','500.00','Cash');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('G 507','HONDA','CIVIC','2008/08/25','550.00','600.00','Cash');
Query OK, 1 row affected (0.05 sec)

mysql> select*
    -> from Owner;
+-------+-----------+-----------+------------+----------+-----------+-----------
-+
| ID_No | Type      | Model_Car | PymtDate   | PymtMade | PymtPrice | PymtType
 |
+-------+-----------+-----------+------------+----------+-----------+-----------
-+
| A 501 | PERODUA   | MYVI      | 1999-05-05 |   450.00 |    600.00 | Cash
 |
| B 502 | PROTON    | PERDANA   | 1999-10-10 |   450.00 |    500.00 | Cash
 |
| C 503 | PROTON    | SAGA      | 2000-10-10 |   550.00 |    550.00 | Instalment
 |
| D 504 | PERODUA   | KELISA    | 2001-10-15 |   550.00 |    650.00 | Cash
 |
| E 505 | PERODUA   | KENARI    | 2003-05-25 |   650.00 |    750.00 | Cash
 |
| F 506 | MITSUBSHI | LANCER    | 2004-05-25 |   450.00 |    500.00 | Cash
 |
| G 507 | HONDA     | CIVIC     | 2008-08-25 |   550.00 |    600.00 | Cash
 |
+-------+-----------+-----------+------------+----------+-----------+-----------
-+
7 rows in set (0.02 sec)

mysql> select Fam_Num,Fam_Name,Street,Type,Model_Car from Family,Owner
    -> where Owner.ID_No = Family.ID_No and type='PERODUA';
+---------+----------+--------------+---------+-----------+
| Fam_Num | Fam_Name | Street       | Type    | Model_Car |
+---------+----------+--------------+---------+-----------+
| 100     | Adawiah  | Kg Pasir Era | PERODUA | MYVI      |
| 103     | Iklima   | Jln Pahlawan | PERODUA | KELISA    |
| 104     | Malikah  | Bdr Sulaiman | PERODUA | KENARI    |
+---------+----------+--------------+---------+-----------+
3 rows in set (0.03 sec)

mysql> select Fam_Name,State,PymtDate,Model_Car,PymtPrice from Family,Owner
    -> where Family.ID_No = Owner.ID_No;
+--------------+--------------+------------+-----------+-----------+
| Fam_Name     | State        | PymtDate   | Model_Car | PymtPrice |
+--------------+--------------+------------+-----------+-----------+
| Adawiah      | Kelantan     | 1999-05-05 | MYVI      |    600.00 |
| Hadiyatullah | Kelantan     | 1999-10-10 | PERDANA   |    500.00 |
| Sharifuddin  | Kelantan     | 2000-10-10 | SAGA      |    550.00 |
| Iklima       | Selangor     | 2001-10-15 | KELISA    |    650.00 |
| Malikah      | Selangor     | 2003-05-25 | KENARI    |    750.00 |
| Nik Hassan   | Selangor     | 2004-05-25 | LANCER    |    500.00 |
| Ahmad        | Kuala Lumpur | 2008-08-25 | CIVIC     |    600.00 |
+--------------+--------------+------------+-----------+-----------+
7 rows in set (0.00 sec)

mysql> select Fam_Name,Fam_Num,City,State,Model_Car,PymtDate from Family,Owner
    -> where Owner.ID_No = Family.ID_No and type='PROTON';
+--------------+---------+------------+----------+-----------+------------+
| Fam_Name     | Fam_Num | City       | State    | Model_Car | PymtDate   |
+--------------+---------+------------+----------+-----------+------------+
| Hadiyatullah | 101     | Kuala Krai | Kelantan | PERDANA   | 1999-10-10 |
| Sharifuddin  | 102     | Kuala Krai | Kelantan | SAGA      | 2000-10-10 |
+--------------+---------+------------+----------+-----------+------------+
2 rows in set (0.00 sec)

mysql> select Fam_Num,street,City,State,Model_Car,PymtDate from Family,Owner
    -> where Owner.ID_No = Family.ID_No and type='PERODUA';
+---------+--------------+------------+----------+-----------+------------+
| Fam_Num | street       | City       | State    | Model_Car | PymtDate   |
+---------+--------------+------------+----------+-----------+------------+
| 100     | Kg Pasir Era | Kuala Krai | Kelantan | MYVI      | 1999-05-05 |
| 103     | Jln Pahlawan | Damansara  | Selangor | KELISA    | 2001-10-15 |
| 104     | Bdr Sulaiman | Port Klang | Selangor | KENARI    | 2003-05-25 |
+---------+--------------+------------+----------+-----------+------------+
3 rows in set (0.00 sec)

mysql> select Fam_Name,street,City,Model_Car,PymtDate,PymtPrice from Family,Owne
r
    -> where Owner.ID_No = Family.ID_No and type='MITSUBSHI';
+------------+--------------+------------+-----------+------------+-----------+
| Fam_Name   | street       | City       | Model_Car | PymtDate   | PymtPrice |
+------------+--------------+------------+-----------+------------+-----------+
| Nik Hassan | Bdr Sulaiman | Port Klang | LANCER    | 2004-05-25 |    500.00 |

+------------+--------------+------------+-----------+------------+-----------+
1 row in set (0.00 sec)

mysql> select Fam_Name,Model_Car,PymtDate from Family,Owner
    -> where Owner.ID_No = Family.ID_No;
+--------------+-----------+------------+
| Fam_Name     | Model_Car | PymtDate   |
+--------------+-----------+------------+
| Adawiah      | MYVI      | 1999-05-05 |
| Hadiyatullah | PERDANA   | 1999-10-10 |
| Sharifuddin  | SAGA      | 2000-10-10 |
| Iklima       | KELISA    | 2001-10-15 |
| Malikah      | KENARI    | 2003-05-25 |
| Nik Hassan   | LANCER    | 2004-05-25 |
| Ahmad        | CIVIC     | 2008-08-25 |
+--------------+-----------+------------+
7 rows in set (0.00 sec)

Database - mysql queries -part9

Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database adi;
Query OK, 1 row affected (0.14 sec)

mysql> use adi;
Database changed
mysql> create table Quiz
    -> (Student_Ic char(20) primary key,
    -> Last_Name char(15),
    -> First_Name char(15),
    -> City char(20),
    -> State char(20),
    -> Age char(2));
Query OK, 0 rows affected (0.17 sec)

mysql> Describe student;
ERROR 1146 (42S02): Table 'adi.student' doesn't exist
mysql> Describe Quiz;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| Student_Ic | char(20) | NO   | PRI |         |       |
| Last_Name  | char(15) | YES  |     | NULL    |       |
| First_Name | char(15) | YES  |     | NULL    |       |
| City       | char(20) | YES  |     | NULL    |       |
| State      | char(20) | YES  |     | NULL    |       |
| Age        | char(2)  | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
6 rows in set (0.05 sec)

mysql> create table Supplier
    -> (Supp_Num char(3) primary key,
    -> Supp_Name char(15),
    -> Street char(20),
    -> City char(20),
    -> Zip_Code char(6),
    -> Balance char(7),
    -> Credit_Limit char(8),
    -> Rep_Num char(3));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into Supplier values
    -> ('148','Hadi','Kg Enggong','Kuala Krai','18000','6550','7500','20');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Supplier values
    -> ('282','Adawiah','Pasir Era','Kuala Krai','18000','431.50','10000','35');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Supplier values
    -> ('356','Ayub','Pasir Mas','K.Baharu','18500','5785','7500','65');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Supplier values
    -> ('408','Ahmad','Pasir Mas','K.Baharu','18500','5285.25','5000','35');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Supplier values
    -> ('462','Mat','Machang','K.Baharu','18502','3412','10000','65');
Query OK, 1 row affected (0.02 sec)

mysql> insert into Supplier values
    -> ('608','Alan','Machang','K.Baharu','18502','12762','15000','20');
Query OK, 1 row affected (0.02 sec)

mysql> insert into Supplier values
    -> ('524','Man','Kuala Krai','K.Baharu','18000','2106','10000','65');
Query OK, 1 row affected (0.02 sec)

mysql> insert into Supplier values
    -> ('687','Wan','Kuala Krai','K.Baharu','18000','2851','5000','35');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Supplier values
    -> ('725','Nik Hassan','Kuala Krai','K.Baharu','18000','248','7500','35');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Supplier values
    -> ('842','Nik Mat','Kuala Krai','K.Baharu','18000','8221','7500','20');
Query OK, 1 row affected (0.01 sec)

mysql> select*
    -> from Supplier;
+----------+------------+------------+------------+----------+---------+--------
------+---------+
| Supp_Num | Supp_Name  | Street     | City       | Zip_Code | Balance | Credit_
Limit | Rep_Num |
+----------+------------+------------+------------+----------+---------+--------
------+---------+
| 148      | Hadi       | Kg Enggong | Kuala Krai | 18000    | 6550    | 7500
      | 20      |
| 282      | Adawiah    | Pasir Era  | Kuala Krai | 18000    | 431.50  | 10000
      | 35      |
| 356      | Ayub       | Pasir Mas  | K.Baharu   | 18500    | 5785    | 7500
      | 65      |
| 408      | Ahmad      | Pasir Mas  | K.Baharu   | 18500    | 5285.25 | 5000
      | 35      |
| 462      | Mat        | Machang    | K.Baharu   | 18502    | 3412    | 10000
      | 65      |
| 524      | Man        | Kuala Krai | K.Baharu   | 18000    | 2106    | 10000
      | 65      |
| 608      | Alan       | Machang    | K.Baharu   | 18502    | 12762   | 15000
      | 20      |
| 687      | Wan        | Kuala Krai | K.Baharu   | 18000    | 2851    | 5000
      | 35      |
| 725      | Nik Hassan | Kuala Krai | K.Baharu   | 18000    | 248     | 7500
      | 35      |
| 842      | Nik Mat    | Kuala Krai | K.Baharu   | 18000    | 8221    | 7500
      | 20      |
+----------+------------+------------+------------+----------+---------+--------
------+---------+
10 rows in set (0.01 sec)


mysql> select Supp_Name,Supp_Name,Balance from Supplier order by balance;
+----------+------------+---------+
| Supp_Num | Supp_Name  | Balance |
+----------+------------+---------+
| 608      | Alan       | 12762   |
| 524      | Man        | 2106    |
| 725      | Nik Hassan | 248     |
| 687      | Wan        | 2851    |
| 462      | Mat        | 3412    |
| 282      | Adawiah    | 431.50  |
| 408      | Ahmad      | 5285.25 |
| 356      | Ayub       | 5785    |
| 148      | Hadi       | 6550    |
| 842      | Nik Mat    | 8221    |
+----------+------------+---------+
10 rows in set (0.02 sec)

mysql> select count(Supp_Name),Sum(Balance) from Supplier;
+-----------------+--------------+
| count(Supp_Num) | Sum(Balance) |
+-----------------+--------------+
|              10 |     47651.75 |
+-----------------+--------------+
1 row in set (0.01 sec)

mysql> select Credit_Limit,Count(*) from Supplier
    -> Group by Credit_Limit;
+--------------+----------+
| Credit_Limit | Count(*) |
+--------------+----------+
| 10000        |        3 |
| 15000        |        1 |
| 5000         |        2 |
| 7500         |        4 |
+--------------+----------+
4 rows in set (0.00 sec)

mysql> select Credit_Limit,Count(*) from Supplier
    -> Group by credit_Limit
    -> having count(*)>1;
+--------------+----------+
| Credit_Limit | Count(*) |
+--------------+----------+
| 10000        |        3 |
| 5000         |        2 |
| 7500         |        4 |
+--------------+----------+
3 rows in set (0.02 sec)

mysql> Alter table Supplier
    -> Drop Zip_Code;
Query OK, 10 rows affected (0.17 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select Credit_Limit,Count(Supp_Num) from Supplier where Rep_Num=20
    -> Group by Credit_limit;
+--------------+-----------------+
| Credit_Limit | Count(Supp_Num) |
+--------------+-----------------+
| 15000        |               1 |
| 7500         |               2 |
+--------------+-----------------+
2 rows in set (0.00 sec)

mysql> select sum(Balance),AVG(Balance),MAX(Balance),MIN(Balance) from Supplier;

+--------------+--------------+--------------+--------------+
| sum(Balance) | AVG(Balance) | MAX(Balance) | MIN(Balance) |
+--------------+--------------+--------------+--------------+
|     47651.75 |     4765.175 | 8221         | 12762        |
+--------------+--------------+--------------+--------------+
1 row in set (0.00 sec)