Make Your Life Easy

Retrieve Folder Option in Windows Xp

2012-06-17T22:07:00.001-07:00

Folder options missing in windows XP.
This happens when your pc is attack by virus.
Eventhough the clean up is done the Folder options will be missing in windows XP. To enable this function.

1.Go to Run –>gpedit.msc

2. User Configuration –> Administrative Templates –> Windows Components –> Windows Explorer
Enable and then Disable “Removes Folder Options menu from Tools menu”

Change TCP/IP settings using cmd

2012-01-15T21:56:00.000-08:00

When you often use a laptop on different netwoks you can very easily change your ip and much more by use this command. You can save the line in a batch file and run the file when you switch between home and work.

set address name="Local Area Connection" static 10.10.69.205 255.255.255.0 10.10.5.1 1

The syntax is:
set address [name=]InterfaceName [source=]{dhcp | static [addr=]IPAddress [mask=]SubnetMask [gateway]

Assigning a Static IP Address

2012-01-15T21:47:00.000-08:00

To assign a Static IP Address to your network connection follow these steps:

Open Network Connections

Right click the connection you wish to assign a Static Address to and choose Properties.

In the Properties box that comes up select "Internet Protocol TCP/IP" and click the Properties button beneath.

In Internet Protocol TCP/IP Properties click dot in "Use the following IP Address".

Enter the IP address you wish to use and OK the change. Click OK on Local Area Connection Properties.

Reboot

Important Tips That Every Windows7 User Must Know

2012-01-15T21:04:00.000-08:00

Tips Every Windows 7 User Needs To Know

Alt + P

In Windows Explorer, this will activates a preview pane of  selected file, be it an image, sound, or video document. This panel is  great for previewing images in your photos directory, obviating the need  for fancier third-party software.

Windows + Up and Windows + Down

If a window isn't maximized, pressing the Windows + Up arrow key will  make it fill your entire screen. Windows + Down arrow will minimize  that active window.

Windows + Shift + Up and Windows + Shift + Down

Hitting these three keys will vertically stretch an active window to  the maximum desktop height (the width of the window, however, will stay  the same). Pressing Windows + Shift+ Down will restore the window to its  previous dimensions.

Windows + + and Windows + -

Pressing the Windows button with either the plus or minus key activates  the Magnifier, letting you zoom in on the entire desktop or open a  rectangular magnifying lens to zoom in on (and out of) parts of screen. You can also customize the Magnifier to follow your mouse  pointer or keyboard cursor.

Windows + Left and Windows + Right

These two shortcuts will make your active window fill up exactly one  half of screen-depending on which arrow key you use. And once a  window is fixed to one side of the screen, you can repeat the shortcut  with the same arrow key to flip it to the other side.

Windows + Home

This shortcut minimizes every open window on your desktop except the  active window. Pressing this shortcut again restores all the minimized  windows.

Windows + T

Like Alt + Tab (still our all-time-favorite Windows shortcut), Windows +  T cycles through thumbnails of open programs via the Taskbar's  peek menu.

Windows + E

Automatically opens up a new Explorer window to show your Libraries folder.

Windows + P

Manage your multiple-monitor setup more efficiently with this handy  shortcut. Windows + P opens a small overlay that lets you configure a  second display or projector. You can switch from a single monitor to  dual-display in either mirror or extend-desktop mode.

Windows + Shift + Left and Windows + Shift + Right

If you're using two or more displays-and you are, aren't  you?-memorize this shortcut to easily move a window from one screen to  the other. The window retains its size and relative position on the new  screen, which is useful when working with multiple documents.

Windows + [Number]

Programs (and new instances) pinned to your Taskbar can be launched  by hitting Windows and the appropriate number key. Windows + 1, for  example, launches the first application in the taskbar, while Windows + 4  will launch the fourth.

Windows + Space
This shortcut performs the same function as moving the mouse to the bottom right of the Taskbar: It makes every active window transparent (save faint outlines) so you can view the desktop underneath.

Netsh winsock reset

2011-12-06T17:54:00.000-08:00

Netsh Winsock Reset is fine command that can use in Microsoft Windows to reset winsock catalog back to default state. This steps able to be used if , all other suggested basic steps been use, such as reset web setting , clear cookies and history and reset ipconfig, flushdns.

or from suggestion:

1.No network connectivity due to registry errors
2. DNS lookup problem
3. Fail to renew the network adapter’s IP address or other DHCP errors
3. Network connectivity issue with limited or no connections message
4. Other networking problems

5. Internet or network problem after removing the adware, spyware, virus, worm, trojan horse, etc
6. Loss network connection after installing/uninstalling adware, spyware, antispam, vpn, firewall or other networking programs
7. Unable to access any webpage or can only access some web pages
8.Pop-up error window with network related problem description

STEPS TO BE DONE:

1)Go to Start and click Run…
2)Type cmd.exe and click OK.
3) Type netsh winsock reset in command prompt and hit ENTER button.

4) Restart the computer in order to complete the reset, after that test to access your network or Internet again.

How to increase your virtual memory

2011-09-19T00:38:00.000-07:00

MEMORY POWER

Have you guys felt that your pc is hanging when open many application at one times.Felt like throwing your pc or laptop away..After this you wont felt like that anymore.You can resolved this issue by increasing the system paging file size also called as virtual memory.This will help to free up enough RAM for other application to run normally

Open system Properties;

click win key + pause key which will open system properties

or right click on my computer and click on properties

Control Panel > System > Advanced > Click on Settings (Under Performance) > Advanced > Change

Here under Drive (Volume label), you have option to select any of your hard disk drive, but the best choice is, select any drive other than C drive for virtual memory.

Set the double Initial & Maximum Size of your Virtual Memory. Here set initial size 700 MB and maximum size up to 1000MB; you can change this size according to the free space available on your hard drive.

Now you can open many application in one time without your desktop or laptop gets hang.

Want to Hide All Icons from the Desktop

2011-08-31T19:01:00.000-07:00

Feel that desktop is messy. Wanted to make it look empty. Then you can try do this little trick.

Start Regedit
Go to HKey_Current_User \ Software\ Microsoft \Windows \ Current Version \ Policies \ Explorer
Right click on the right panel and add a New / DWORD
Name it NoDesktop
Give it a value of 1
Logoff or Reboot the computer
Now all icons are hidden on the desktop

Want to add Items to the Start Button using Registry?

2011-08-31T18:04:00.000-07:00

Before you start play with your registry, make sure you have backup or export your windows registry keys on backup drive, because a small mistake in Registry it will cost you to reformat your computer or laptop..so be extra careful with mr.registry.

First click on Start button then type Regedit in Run option

In Registry Editor look at left hand panel, and find HKey_Classes_Root / Directory / Shell

Then Right click on Shell folder option and add any new key with descriptive name and Press Enter.

Next Right click on that key and give the full path of that program you are interested to add in start button. For example if you want to add MS excel, then full path may be C:\program file\msoffice\msaccess.EXE.

Close all open programs and access will appear in your computer Start button.

Files on External/Flash Drive Changed to Shortcuts Virus

2011-08-31T16:42:00.000-07:00

Have ever your files in flash drive turn to shortcut and you unable to open them?
Now you can clear the shortcut's to view them normally back

In order to do this process you must not format the flash drive. because if you have formatted the flash drive , all the files will have been deleted.

Click on “Start” –>Run–>type cmd and click on OK.
Check your external Drive letter in My Computer
Here I assume your external drive as E:
Enter this command.
attrib -h -r -s /s /d g:*.*
*Delete the unnecessary shortcuts.

P/S:Please replace the letter of E with your flash drive .

Internet Technology Blog

2011-08-16T21:09:00.000-07:00

Internet Technology Blog

a blog that related with computer networks and network security

The Recycle Bin's Name!

2011-08-16T20:55:00.000-07:00

Want to change The Recycle Bin's Name!

Now you can change the name of Recycle Bin of your system . It is quite interesting but it need some work around

step 1:click start button -> click run option

step 2 :Type here regedit.exe and press enter

step 3: Regedit editor

step 4: regedit editor open HKEY_CLASSES_ROOT folder, In this folder open the CLSID folder then open the {645FF040-5081-101B-9F08-00AA002F954E} folder >ShellFolder folder at last.

step 5: Change the data value from "40 01 00 20" to "70 01 00 20".

step 6: Restart your pc and you will find the option to rename Recycle bin name after clicking on Recycle Bin Icon. And now you are able to change the recycle bin name into anything you wish.

Fairy Chat server

2011-07-16T08:39:00.000-07:00

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

Start Windows* 98/Me/2000/XP.
Copy FairyChatServer and ClientWindow source code in notepad and save it in java bin according to the class name .
Open command prompt and compile FairyChatServer.java
Then Open a different command prompt an compile ClientWindow.java.
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

2011-07-16T06:59:00.001-07:00

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

2011-07-16T06:57:00.001-07:00

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

2011-07-16T06:54:00.001-07:00

Database - mysql queries -part9

2011-07-16T06:53:00.001-07:00

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)

Database - mysql queries -part8

2011-07-16T06:52:00.005-07:00

Enter password: *****

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6 to server version: 5.0.27-community-nt

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

mysql> Create database BookStoreSystem;

Query OK, 1 row affected (0.00 sec)

mysql> use BookStoreSystem;

Database changed

mysql> Create Table Branch

-> (Branch_Num Decimal(2) primary key,

-> Branch_name char(50),

-> Branch_Location char(50),

-> Num_Employees Decimal(2));

Query OK, 0 rows affected (0.06 sec)

mysql> Create Table Publisher

-> (Publisher_Code char(3) primary key,

-> Publisher_Name char(25),

-> City char(20));

Query OK, 0 rows affected (0.02 sec)

mysql> Create Table Author

-> (Author_Num decimal(2) primary key,

-> Author_last(12),

-> Author_last char(12),

-> Author_First char(10));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near '(12),

Author_last char(12),

Author_First char(10))' at line 3

mysql> Create Table Author

-> (Author_Num decimal(2) primary key,

-> Author_last char(12),

-> Author_First char(10));

Query OK, 0 rows affected (0.01 sec)

mysql> Create Table Book

-> (Book_Code char(4) primary key,

-> Title char (40),

-> Publisher_Code char(25),

-> Type char(3),

-> Price Decimal(4),

-> Paperback char(1));

Query OK, 0 rows affected (0.03 sec)

mysql> Create Table Wrote

-> (Book_Code char(4) primary key,

-> Author_Num Decimal(2),

-> Sequence Decimal(1));

Query OK, 0 rows affected (0.03 sec)

mysql> Create Table Inventory

-> (Book_Code char(4) primary key,

-> Branch_Num Decimal(2),

-> On_Hand Decimal(2));

Query OK, 0 rows affected (0.01 sec)

mysql> Show columns from BookStoreSystem;

ERROR 1146 (42S02): Table 'bookstoresystem.bookstoresystem' doesn't exist

mysql> show columns

-> fromBookStoreSystem;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near 'fromB

ookStoreSystem' at line 2

mysql> show columns

-> from BookStoreSystem;

ERROR 1146 (42S02): Table 'bookstoresystem.bookstoresystem' doesn't exist

mysql> show columns

-> from Branch;

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

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

| Branch_Num | decimal(2,0) | NO | PRI | | |

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

4 rows in set (0.00 sec)

mysql> show columns

-> from Publisher;

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

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

| Publisher_Code | char(3) | NO | PRI | | |

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

3 rows in set (0.00 sec)

mysql> show columns

-> from Author;

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

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

| Author_Num | decimal(2,0) | NO | PRI | | |

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

3 rows in set (0.00 sec)

mysql> show columns

-> from Book;

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

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

| Book_Code | char(4) | NO | PRI | | |

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

6 rows in set (0.01 sec)

mysql> show columns

-> from Wrote;

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

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

| Book_Code | char(4) | NO | PRI | | |

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

3 rows in set (0.00 sec)

mysql> show columns

-> from Inventory;

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

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

| Book_Code | char(4) | NO | PRI | | |

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

3 rows in set (0.02 sec)

mysql>

Database - mysql queries -part7

2011-07-16T06:47:00.000-07:00

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> use BookStoreSystem
Database changed
mysql> select *
    -> From Branch;
+------------+-------------------+-----------------+---------------+
| Branch_Num | Branch_Name       | Branch_Location | Num_Employees |
+------------+-------------------+-----------------+---------------+
|          1 | Henry Downtown    | 16 Riverview    |            10 |
|          2 | Henry On The Hill | 1289 Bedford    |             6 |
|          3 | Henry Brentwood   | Brentwood Mall |            15 |
|          4 | Henry Eastsore    | Eastshore Mall |             9 |
+------------+-------------------+-----------------+---------------+
4 rows in set (0.03 sec)

mysql> select *
    -> From Publisher;
+----------------+--------------------------+--------------+
| Publisher_Code | Publisher_Name           | City         |
+----------------+--------------------------+--------------+
| AH             | Arkham House             | Sauk City WI |
| AP             | Areade Publishing        | New York     |
| BA             | Basic Books              | Boulder CO   |
| BP             | Berkley publishing       | Boston       |
| BY             | Back Bay Books           | New York     |
| CT             | Couse Technology         | Boston       |
| FA             | Fawcett Books            | New York     |
| FS             | Farrar Straus and Giroux | New York     |
| HC             | HarperCollins Publisher | New York     |
| JP             | Jove Publication         | New York     |
| JT             | Jeremy P.Tarcher         | Los Angeles |
| LB             | Lb Bokks                 | New York     |
| MP             | McPherson and Co         | Kingston     |
| PE             | Penguin USA              | New York     |
| PL             | Plume                    | New York     |
| PU             | Putnam Publishing Group | New York     |
| RH             | Random House             | New York     |
| SB             | Schoken Books            | New York     |
| SG             | Scribner                 | New York     |
| SS             | Simon and Schuster       | New York     |
| ST             | Scholastic Trade         | New York     |
| TA             | Taunton Press            | Newtown CT   |
| TB             | Tor Books                | New York     |
| TH             | Thames and Hudson        | New York     |
| TO             | Touchstone Books         | Westport CT |
| VB             | Vintage Books            | New York     |
| WN             | W.W.Norton               | New York     |
| WP             | Westview Press           | Boulder CO   |
+----------------+--------------------------+--------------+
28 rows in set (0.02 sec)

mysql> select *
    -> From Author;
+------------+-------------+--------------+
| Author_Num | Author_Last | Author_First |
+------------+-------------+--------------+
|          1 | Morrison    | Toni         |
|          2 | Solotaroff | paul         |
|          3 | Vintage     | vernor       |
|          4 | Francis     | Dick         |
|          5 | Straub      | Peter        |
|          6 | King        | Stephen      |
|          7 | Pratt       | Philip       |
|          8 | Chase       | Truddi       |
|          9 | Collins     | bradley      |
|         10 | Heller      | Joseph       |
|         11 | Will        | Gary         |
|         12 | Hofstadter | Douglas R    |
|         13 | Lee         | Harper       |
|         14 | Ambrose     | Stephen E    |
|         15 | Rowling     | J.K          |
|         16 | Salinger    | J.D          |
|         17 | Heaney      | Seamus       |
|         18 | Camus       | Albert       |
|         19 | Collins,Jr | bradley      |
|         20 | steinbeck   | John         |
|         21 | Castelman   | Riva         |
|         22 | Owen        | Barbara      |
|         23 | ORourke     | Randy        |
|         24 | Kidder      | Tracy        |
|         25 | Schleining | Lon          |
+------------+-------------+--------------+
25 rows in set (0.03 sec)

mysql> select *
    -> From Book;
+-----------+------------------------------------------+----------------+------+
-------+-----------+
| Book_Code | Title                                    | Publisher_Code | Type |
price | Peperback |
+-----------+------------------------------------------+----------------+------+
-------+-----------+
| 0180      | A Deepness in the Sky                    | TB             | SFI |
7.00 | Y         |
| 0189      | Magic terror                             | FA             | HOR |
8.00 | Y         |
| 0200      | The Stranger                             | VB             | FIC |
8.00 | Y         |
| 0378      | Venice                                   | SS             | ART |
24.50 | N         |
| 079X      | Second Wind                              | PU             | MYS |
24.95 | N         |
| 0808      | The Edge                                 | JP             | MYS |
6.99 | N         |
| 1351      | Dreamcatcher A Novel                     | SC             | HOR |
19.60 | N         |
| 1382      | Treasure Chests                          | TA             | ART |
24.46 | N         |
| 138X      | Beloved                                  | PL             | FIC |
12.95 | Y         |
| 2226      | Harry Potter and the Prisoner of Azkaban | ST             | SFI |
13.96 | N         |
| 2281      | Van Gogh and Gauguin                     | WP             | ART |
21.00 | N         |
| 2766      | Of Mice and Men                          | PE             | FIC |
6.95 | Y         |
| 2908      | Electric                                 | FS             | POE |
14.00 | N         |
| 3350      | Group Six People in Search of a life     | BP             | PSY |
10.40 | Y         |
| 3743      | Nine Stories                             | LB             | FIC |
5.99 | Y         |
| 3906      | The Soul of a New Machine                | BY             | SCI |
11.16 | Y         |
| 5163      | Travels with Charley                     | PE             | TRA |
7.95 | Y         |
| 5790      | Catch 22                                 | SC             | FIC |
12.00 | Y         |
| 6128      | jazz                                     | PL             | FIC |
12.95 | Y         |
| 6328      | Band of Brothers                         | TO             | HIS |
9.60 | Y         |
| 669X      | AGuide to SQL                            | CT             | CMP |
37.95 | Y         |
| 6908      | Franny and Zooey                         | LB             | FIC |
5.99 | Y         |
| 7405      | East of Eden                             | PE             | FIC |
12.95 | Y         |
| 7443      | Harry Potter and the Goblet of Fire      | ST             | SFI |
18.16 | N         |
| 7559      | The Fall                                 | VB             | FIC |
8.00 | Y         |
| 8092      | Godel Escher Bach                        | BA             | PHI |
14.00 | Y         |
| 8720      | When Rabbit Howls                        | JA             | PSY |
6.29 | Y         |
| 9611      | Black House                              | RH             | HOR |
18.81 | N         |
| 9627      | Song of Solomon                          | PL             | FIC |
14.00 | Y         |
| 9701      | The Grapes of Wrath                      | PE             | FIC |
13.00 | Y         |
| 9880      | Slay Ride                                | JP             | MYS |
6.99 | Y         |
| 9883      | The Catcher in the Rye                   | LB             | FIC |
5.99 | Y         |
| 9931      | To Kill a Mockingbird                    | HC             | FIC |
18.00 | N         |
+-----------+------------------------------------------+----------------+------+
-------+-----------+
33 rows in set (0.03 sec)

mysql> select *
    -> From Wrote;
+-----------+------------+----------+
| Book_Code | Author_Num | Sequence |
+-----------+------------+----------+
| 0180      |          3 |        1 |
| 0189      |          5 |        1 |
| 0200      |         18 |        1 |
| 0378      |         11 |        1 |
| 079X      |          4 |        1 |
| 0808      |          4 |        1 |
| 1351      |          6 |        1 |
| 1382      |         23 |        2 |
| 138X      |          1 |        1 |
| 2226      |         15 |        1 |
| 2281      |         19 |        2 |
| 2766      |         20 |        1 |
| 2908      |         17 |        1 |
| 3350      |          2 |        1 |
| 3743      |         16 |        1 |
| 3906      |         24 |        1 |
| 5163      |         20 |        1 |
| 5790      |         10 |        1 |
| 6128      |          1 |        1 |
| 6328      |         14 |        1 |
| 669X      |          7 |        1 |
| 6908      |         16 |        1 |
| 7405      |         20 |        1 |
| 7443      |         15 |        1 |
| 7559      |         18 |        1 |
| 8092      |         12 |        1 |
| 8720      |          8 |        1 |
| 9611      |          6 |        1 |
| 9627      |          1 |        1 |
| 9701      |         20 |        1 |
| 9882      |          4 |        1 |
| 9883      |         16 |        1 |
| 9931      |         13 |        1 |
+-----------+------------+----------+
33 rows in set (0.05 sec)

mysql> select *
    -> From Inventory;
+-----------+------------+---------+
| Book_Code | Branch_Num | On_Hand |
+-----------+------------+---------+
| 0180      |          1 |       2 |
| 0189      |          2 |       2 |
| 0200      |          2 |       3 |
| 0378      |          3 |       2 |
| 079X      |          2 |       1 |
| 0808      |          2 |       1 |
| 1351      |          3 |       2 |
| 1382      |          2 |       1 |
| 138X      |          2 |       3 |
| 2226      |          3 |       2 |
| 2281      |          4 |       3 |
| 2766      |          3 |       2 |
| 2908      |          4 |       1 |
| 3350      |          1 |       2 |
| 3743      |          2 |       1 |
| 3906      |          3 |       2 |
| 5163      |          1 |       1 |
| 5790      |          4 |       2 |
| 6128      |          3 |       3 |
| 6328      |          2 |       2 |
| 669X      |          1 |       1 |
| 6908      |          2 |       2 |
| 7405      |          3 |       2 |
| 7443      |          4 |       1 |
| 7559      |          2 |       2 |
| 8092      |          3 |       1 |
| 8720      |          1 |       3 |
| 9611      |          1 |       2 |
| 9627      |          3 |       5 |
| 9701      |          2 |       1 |
| 9882      |          3 |       3 |
| 9883      |          4 |       2 |
| 9931      |          1 |       2 |
+-----------+------------+---------+
33 rows in set (0.03 sec)

mysql>

Database - mysql queries -part6

2011-07-16T06:44:00.001-07:00

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> use bookstoresystem
Database changed
mysql> select *
    -> from publisher;
+----------------+--------------------------+--------------+
| Publisher_Code | Publisher_Name           | City         |
+----------------+--------------------------+--------------+
| AH             | Arkham House             | Sauk City WI |
| AP             | Areade Publishing        | New York     |
| BA             | Basic Books              | Boulder CO   |
| BP             | Berkley publishing       | Boston       |
| BY             | Back Bay Books           | New York     |
| CT             | Couse Technology         | Boston       |
| FA             | Fawcett Books            | New York     |
| FS             | Farrar Straus and Giroux | New York     |
| HC             | HarperCollins Publisher | New York     |
| JP             | Jove Publication         | New York     |
| JT             | Jeremy P.Tarcher         | Los Angeles |
| LB             | Lb Bokks                 | New York     |
| MP             | McPherson and Co         | Kingston     |
| PE             | Penguin USA              | New York     |
| PL             | Plume                    | New York     |
| PU             | Putnam Publishing Group | New York     |
| RH             | Random House             | New York     |
| SB             | Schoken Books            | New York     |
| SG             | Scribner                 | New York     |
| SS             | Simon and Schuster       | New York     |
| ST             | Scholastic Trade         | New York     |
| TA             | Taunton Press            | Newtown CT   |
| TB             | Tor Books                | New York     |
| TH             | Thames and Hudson        | New York     |
| TO             | Touchstone Books         | Westport CT |
| VB             | Vintage Books            | New York     |
| WN             | W.W.Norton               | New York     |
| WP             | Westview Press           | Boulder CO   |
+----------------+--------------------------+--------------+
28 rows in set (0.05 sec)

mysql> select city from publisher;
+--------------+
| city         |
+--------------+
| Sauk City WI |
| New York     |
| Boulder CO   |
| Boston       |
| New York     |
| Boston       |
| New York     |
| New York     |
| New York     |
| New York     |
| Los Angeles |
| New York     |
| Kingston     |
| New York     |
| New York     |
| New York     |
| New York     |
| New York     |
| New York     |
| New York     |
| New York     |
| Newtown CT   |
| New York     |
| New York     |
| Westport CT |
| New York     |
| New York     |
| Boulder CO   |
+--------------+
28 rows in set (0.00 sec)

mysql> select*
    -> from book;
+-----------+------------------------------------------+----------------+------+
-------+-----------+
| Book_Code | Title                                    | Publisher_Code | Type |
price | Peperback |
+-----------+------------------------------------------+----------------+------+
-------+-----------+
| 0180      | A Deepness in the Sky                    | TB             | SFI |
7.00 | Y         |
| 0189      | Magic terror                             | FA             | HOR |
8.00 | Y         |
| 0200      | The Stranger                             | VB             | FIC |
8.00 | Y         |
| 0378      | Venice                                   | SS             | ART |
24.50 | N         |
| 079X      | Second Wind                              | PU             | MYS |
24.95 | N         |
| 0808      | The Edge                                 | JP             | MYS |
6.99 | N         |
| 1351      | Dreamcatcher A Novel                     | SC             | HOR |
19.60 | N         |
| 1382      | Treasure Chests                          | TA             | ART |
24.46 | N         |
| 138X      | Beloved                                  | PL             | FIC |
12.95 | Y         |
| 2226      | Harry Potter and the Prisoner of Azkaban | ST             | SFI |
13.96 | N         |
| 2281      | Van Gogh and Gauguin                     | WP             | ART |
21.00 | N         |
| 2766      | Of Mice and Men                          | PE             | FIC |
6.95 | Y         |
| 2908      | Electric                                 | FS             | POE |
14.00 | N         |
| 3350      | Group Six People in Search of a life     | BP             | PSY |
10.40 | Y         |
| 3743      | Nine Stories                             | LB             | FIC |
5.99 | Y         |
| 3906      | The Soul of a New Machine                | BY             | SCI |
11.16 | Y         |
| 5163      | Travels with Charley                     | PE             | TRA |
7.95 | Y         |
| 5790      | Catch 22                                 | SC             | FIC |
12.00 | Y         |
| 6128      | jazz                                     | PL             | FIC |
12.95 | Y         |
| 6328      | Band of Brothers                         | TO             | HIS |
9.60 | Y         |
| 669X      | AGuide to SQL                            | CT             | CMP |
37.95 | Y         |
| 6908      | Franny and Zooey                         | LB             | FIC |
5.99 | Y         |
| 7405      | East of Eden                             | PE             | FIC |
12.95 | Y         |
| 7443      | Harry Potter and the Goblet of Fire      | ST             | SFI |
18.16 | N         |
| 7559      | The Fall                                 | VB             | FIC |
8.00 | Y         |
| 8092      | Godel Escher Bach                        | BA             | PHI |
14.00 | Y         |
| 8720      | When Rabbit Howls                        | JA             | PSY |
6.29 | Y         |
| 9611      | Black House                              | RH             | HOR |
18.81 | N         |
| 9627      | Song of Solomon                          | PL             | FIC |
14.00 | Y         |
| 9701      | The Grapes of Wrath                      | PE             | FIC |
13.00 | Y         |
| 9880      | Slay Ride                                | JP             | MYS |
6.99 | Y         |
| 9883      | The Catcher in the Rye                   | LB             | FIC |
5.99 | Y         |
| 9931      | To Kill a Mockingbird                    | HC             | FIC |
18.00 | N         |
+-----------+------------------------------------------+----------------+------+
-------+-----------+
33 rows in set (0.03 sec)

mysql> select title peperback from book;
+------------------------------------------+
| peperback                                |
+------------------------------------------+
| A Deepness in the Sky                    |
| Magic terror                             |
| The Stranger                             |
| Venice                                   |
| Second Wind                              |
| The Edge                                 |
| Dreamcatcher A Novel                     |
| Treasure Chests                          |
| Beloved                                  |
| Harry Potter and the Prisoner of Azkaban |
| Van Gogh and Gauguin                     |
| Of Mice and Men                          |
| Electric                                 |
| Group Six People in Search of a life     |
| Nine Stories                             |
| The Soul of a New Machine                |
| Travels with Charley                     |
| Catch 22                                 |
| jazz                                     |
| Band of Brothers                         |
| AGuide to SQL                            |
| Franny and Zooey                         |
| East of Eden                             |
| Harry Potter and the Goblet of Fire      |
| The Fall                                 |
| Godel Escher Bach                        |
| When Rabbit Howls                        |
| Black House                              |
| Song of Solomon                          |
| The Grapes of Wrath                      |
| Slay Ride                                |
| The Catcher in the Rye                   |
| To Kill a Mockingbird                    |
+------------------------------------------+
33 rows in set (0.00 sec)

mysql> select publisher_name, city from publisher;
+--------------------------+--------------+
| publisher_name           | city         |
+--------------------------+--------------+
| Arkham House             | Sauk City WI |
| Areade Publishing        | New York     |
| Basic Books              | Boulder CO   |
| Berkley publishing       | Boston       |
| Back Bay Books           | New York     |
| Couse Technology         | Boston       |
| Fawcett Books            | New York     |
| Farrar Straus and Giroux | New York     |
| HarperCollins Publisher | New York     |
| Jove Publication         | New York     |
| Jeremy P.Tarcher         | Los Angeles |
| Lb Bokks                 | New York     |
| McPherson and Co         | Kingston     |
| Penguin USA              | New York     |
| Plume                    | New York     |
| Putnam Publishing Group | New York     |
| Random House             | New York     |
| Schoken Books            | New York     |
| Scribner                 | New York     |
| Simon and Schuster       | New York     |
| Scholastic Trade         | New York     |
| Taunton Press            | Newtown CT   |
| Tor Books                | New York     |
| Thames and Hudson        | New York     |
| Touchstone Books         | Westport CT |
| Vintage Books            | New York     |
| W.W.Norton               | New York     |
| Westview Press           | Boulder CO   |
+--------------------------+--------------+
28 rows in set (0.00 sec)

mysql> select*
    -> from author;
+------------+-------------+--------------+
| Author_Num | Author_Last | Author_First |
+------------+-------------+--------------+
|          1 | Morrison    | Toni         |
|          2 | Solotaroff | paul         |
|          3 | Vintage     | vernor       |
|          4 | Francis     | Dick         |
|          5 | Straub      | Peter        |
|          6 | King        | Stephen      |
|          7 | Pratt       | Philip       |
|          8 | Chase       | Truddi       |
|          9 | Collins     | bradley      |
|         10 | Heller      | Joseph       |
|         11 | Will        | Gary         |
|         12 | Hofstadter | Douglas R    |
|         13 | Lee         | Harper       |
|         14 | Ambrose     | Stephen E    |
|         15 | Rowling     | J.K          |
|         16 | Salinger    | J.D          |
|         17 | Heaney      | Seamus       |
|         18 | Camus       | Albert       |
|         19 | Collins,Jr | bradley      |
|         20 | steinbeck   | John         |
|         21 | Castelman   | Riva         |
|         22 | Owen        | Barbara      |
|         23 | ORourke     | Randy        |
|         24 | Kidder      | Tracy        |
|         25 | Schleining | Lon          |
+------------+-------------+--------------+
25 rows in set (0.02 sec)

mysql> insert into author value
    -> ('26','Jemes','Peter');
Query OK, 1 row affected (0.05 sec)

mysql>

Database - mysql queries -part5

2011-07-16T06:43:00.001-07:00

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> use BookStoreSystem

Database changed

mysql> select *

-> from Branch;

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

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

| 1 | Henry Downtown | 16 Riverview | 10 |

| 2 | Henry On The Hill | 1289 Bedford | 6 |

| 3 | Henry Brentwood | Brentwood Mall | 15 |

| 4 | Henry Eastsore | Eastshore Mall | 9 |

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

4 rows in set (0.05 sec)

mysql> select *

-> from Publisher;

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

| Publisher_Code | Publisher_Name | City |

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

| AH | Arkham House | Sauk City WI |

| AP | Areade Publishing | New York |

| BA | Basic Books | Boulder CO |

| BP | Berkley publishing | Boston |

| BY | Back Bay Books | New York |

| CT | Couse Technology | Boston |

| FA | Fawcett Books | New York |

| FS | Farrar Straus and Giroux | New York |

| HC | HarperCollins Publisher | New York |

| JP | Jove Publication | New York |

| JT | Jeremy P.Tarcher | Los Angeles |

| LB | Lb Bokks | New York |

| MP | McPherson and Co | Kingston |

| PE | Penguin USA | New York |

| PL | Plume | New York |

| PU | Putnam Publishing Group | New York |

| RH | Random House | New York |

| SB | Schoken Books | New York |

| SG | Scribner | New York |

| SS | Simon and Schuster | New York |

| ST | Scholastic Trade | New York |

| TA | Taunton Press | Newtown CT |

| TB | Tor Books | New York |

| TH | Thames and Hudson | New York |

| TO | Touchstone Books | Westport CT |

| VB | Vintage Books | New York |

| WN | W.W.Norton | New York |

| WP | Westview Press | Boulder CO |

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

28 rows in set (0.03 sec)

mysql> select *

-> from Author;

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

| Author_Num | Author_Last | Author_First |

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

| 1 | Morrison | Toni |

| 2 | Solotaroff | paul |

| 3 | Vintage | vernor |

| 4 | Francis | Dick |

| 5 | Straub | Peter |

| 6 | King | Stephen |

| 7 | Pratt | Philip |

| 8 | Chase | Truddi |

| 9 | Collins | bradley |

| 10 | Heller | Joseph |

| 11 | Will | Gary |

| 12 | Hofstadter | Douglas R |

| 13 | Lee | Harper |

| 14 | Ambrose | Stephen E |

| 15 | Rowling | J.K |

| 16 | Salinger | J.D |

| 17 | Heaney | Seamus |

| 18 | Camus | Albert |

| 19 | Collins,Jr | bradley |

| 20 | steinbeck | John |

| 21 | Castelman | Riva |

| 22 | Owen | Barbara |

| 23 | ORourke | Randy |

| 24 | Kidder | Tracy |

| 25 | Schleining | Lon |

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

25 rows in set (0.05 sec)

Enter password: ******

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.0.37-community-nt MySQL Community Edition (GPL)

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

mysql> use BookStoreSystem

Database changed

mysql> select *

-> From Book;

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

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

price | Peperback |

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

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

| 0180 | A Deepness in the Sky | TB | SFI |

7.00 | Y |

| 0189 | Magic terror | FA | HOR |

8.00 | Y |

| 0200 | The Stranger | VB | FIC |

8.00 | Y |

| 0378 | Venice | SS | ART |

24.50 | N |

| 079X | Second Wind | PU | MYS |

24.95 | N |

| 0808 | The Edge | JP | MYS |

6.99 | N |

| 1351 | Dreamcatcher A Novel | SC | HOR |

19.60 | N |

| 1382 | Treasure Chests | TA | ART |

24.46 | N |

| 138X | Beloved | PL | FIC |

12.95 | Y |

| 2226 | Harry Potter and the Prisoner of Azkaban | ST | SFI |

13.96 | N |

| 2281 | Van Gogh and Gauguin | WP | ART |

21.00 | N |

| 2766 | Of Mice and Men | PE | FIC |

6.95 | Y |

| 2908 | Electric | FS | POE |

14.00 | N |

| 3350 | Group Six People in Search of a life | BP | PSY |

10.40 | Y |

| 3743 | Nine Stories | LB | FIC |

5.99 | Y |

| 3906 | The Soul of a New Machine | BY | SCI |

11.16 | Y |

| 5163 | Travels with Charley | PE | TRA |

7.95 | Y |

| 5790 | Catch 22 | SC | FIC |

12.00 | Y |

| 6128 | jazz | PL | FIC |

12.95 | Y |

| 6328 | Band of Brothers | TO | HIS |

9.60 | Y |

| 669X | AGuide to SQL | CT | CMP |

37.95 | Y |

| 6908 | Franny and Zooey | LB | FIC |

5.99 | Y |

| 7405 | East of Eden | PE | FIC |

12.95 | Y |

| 7443 | Harry Potter and the Goblet of Fire | ST | SFI |

18.16 | N |

| 7559 | The Fall | VB | FIC |

8.00 | Y |

| 8092 | Godel Escher Bach | BA | PHI |

14.00 | Y |

| 8720 | When Rabbit Howls | JA | PSY |

6.29 | Y |

| 9611 | Black House | RH | HOR |

18.81 | N |

| 9627 | Song of Solomon | PL | FIC |

14.00 | Y |

| 9701 | The Grapes of Wrath | PE | FIC |

13.00 | Y |

| 9880 | Slay Ride | JP | MYS |

6.99 | Y |

| 9883 | The Catcher in the Rye | LB | FIC |

5.99 | Y |

| 9931 | To Kill a Mockingbird | HC | FIC |

18.00 | N |

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

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

33 rows in set (0.00 sec)

mysql> Select *

-> From Wrote;

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

| Book_Code | Author_Num | Sequence |

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

| 0180 | 3 | 1 |

| 0189 | 5 | 1 |

| 0200 | 18 | 1 |

| 0378 | 11 | 1 |

| 079X | 4 | 1 |

| 0808 | 4 | 1 |

| 1351 | 6 | 1 |

| 1382 | 23 | 2 |

| 138X | 1 | 1 |

| 2226 | 15 | 1 |

| 2281 | 19 | 2 |

| 2766 | 20 | 1 |

| 2908 | 17 | 1 |

| 3350 | 2 | 1 |

| 3743 | 16 | 1 |

| 3906 | 24 | 1 |

| 5163 | 20 | 1 |

| 5790 | 10 | 1 |

| 6128 | 1 | 1 |

| 6328 | 14 | 1 |

| 669X | 7 | 1 |

| 6908 | 16 | 1 |

| 7405 | 20 | 1 |

| 7443 | 15 | 1 |

| 7559 | 18 | 1 |

| 8092 | 12 | 1 |

| 8720 | 8 | 1 |

| 9611 | 6 | 1 |

| 9627 | 1 | 1 |

| 9701 | 20 | 1 |

| 9882 | 4 | 1 |

| 9883 | 16 | 1 |

| 9931 | 13 | 1 |

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

33 rows in set (0.00 sec)

mysql> Select *

-> From Inventory;

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

| Book_Code | Branch_Num | On_Hand |

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

| 0180 | 1 | 2 |

| 0189 | 2 | 2 |

| 0200 | 2 | 3 |

| 0378 | 3 | 2 |

| 079X | 2 | 1 |

| 0808 | 2 | 1 |

| 1351 | 3 | 2 |

| 1382 | 2 | 1 |

| 138X | 2 | 3 |

| 2226 | 3 | 2 |

| 2281 | 4 | 3 |

| 2766 | 3 | 2 |

| 2908 | 4 | 1 |

| 3350 | 1 | 2 |

| 3743 | 2 | 1 |

| 3906 | 3 | 2 |

| 5163 | 1 | 1 |

| 5790 | 4 | 2 |

| 6128 | 3 | 3 |

| 6328 | 2 | 2 |

| 669X | 1 | 1 |

| 6908 | 2 | 2 |

| 7405 | 3 | 2 |

| 7443 | 4 | 1 |

| 7559 | 2 | 2 |

| 8092 | 3 | 1 |

| 8720 | 1 | 3 |

| 9611 | 1 | 2 |

| 9627 | 3 | 5 |

| 9701 | 2 | 1 |

| 9882 | 3 | 3 |

| 9883 | 4 | 2 |

| 9931 | 1 | 2 |

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

33 rows in set (0.00 sec)

Database - mysql queries -part4

2011-07-16T06:42:00.000-07:00

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> use adi;
Database changed
mysql> create table Vehicle
    -> (Car_No char(10) primary key,
    -> Type char(15),
    -> Model char(10),
    -> Owner_ID char(10));
Query OK, 0 rows affected (0.17 sec)

mysql> insert into Vehicle values
    -> ('WAC5887','PERODUA','MYVI','123456');
Query OK, 1 row affected (0.06 sec)

mysql> insert into Vehicle values
    -> ('BFG1254','PERODUA','KELISA','452114');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Vehicle values
    -> ('TF81','HONDA','CIVIC','213321');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Vehicle values
    -> ('AR15','MITSUBSHI','LANCER','150819');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Vehicle values
    -> ('BCF1007','PERODUA','KENARI','234540');
Query OK, 1 row affected (0.05 sec)

mysql> select*
    -> from Vehicle;
+---------+-----------+--------+----------+
| Car_No | Type      | Model | Owner_ID |
+---------+-----------+--------+----------+
| AR15    | MITSUBSHI | LANCER | 150819   |
| BCF1007 | PERODUA   | KENARI | 234540   |
| BFG1254 | PERODUA   | KELISA | 452114   |
| TF81    | HONDA     | CIVIC | 213321   |
| WAC5887 | PERODUA   | MYVI   | 123456   |
+---------+-----------+--------+----------+
5 rows in set (0.03 sec)

mysql> create table Owner
    -> (Owner_ID char(8) primary key,
    -> Name char(15),
    -> PymtDate Date,
    -> PymtMade decimal(8,2),
    -> ActualPrice decimal(8,2),
    -> PymtType decimal(8,2),
    -> Address char(15));
Query OK, 0 rows affected (0.14 sec)

mysql> Alter table Owner modify PymtType char(15);
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into Owner values
    -> ('123456','Harrison','1999/01/23','610.00','610.00','Cash','Bangi');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('452114','Baker','1999/01/31','450.00','600.00','Installment','Gombak');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('213321','Aziz','1999/01/31','350.00','700.00','Installment','Klang');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Owner values
    -> ('150819','Ackim','1999/01/25','650.00','650.00','Cash','Sentul');
Query OK, 1 row affected (0.03 sec)

mysql> select*
    -> from Owner;
+----------+----------+------------+----------+-------------+-------------+-----
----+
| Owner_ID | Name     | PymtDate   | PymtMade | ActualPrice | PymtType    | Addr
ess |
+----------+----------+------------+----------+-------------+-------------+-----
----+
| 123456   | Harrison | 1999-01-23 |   610.00 |      610.00 | Cash        | Bang
i   |
| 150819   | Ackim    | 1999-01-25 |   650.00 |      650.00 | Cash        | Sent
ul |
| 213321   | Aziz     | 1999-01-31 |   350.00 |      700.00 | Installment | Klan
g   |
| 452114   | Baker    | 1999-01-31 |   450.00 |      600.00 | Installment | Gomb
ak |
+----------+----------+------------+----------+-------------+-------------+-----
----+
4 rows in set (0.01 sec)

mysql> insert into Vehicle values
    -> ('DAU4019','HONDA','WAVE','5775');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Vehicle values
    -> ('DBF15','PERODUA','VIVA','57775');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Vehicle values
    -> ('DBB32','PERODUA','KANCIL','57875');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Vehicle values
    -> ('DBB321','HONDA','SIVICMAYAT','5755');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Vehicle values
    -> ('DBG123','PERODUA','PERSONA','85585');
Query OK, 1 row affected (0.05 sec)

mysql> select*
    -> from Vehicle;
+---------+-----------+------------+----------+
| Car_No | Type      | Model      | Owner_ID |
+---------+-----------+------------+----------+
| AR15    | MITSUBSHI | LANCER     | 150819   |
| BCF1007 | PERODUA   | KENARI     | 234540   |
| BFG1254 | PERODUA   | KELISA     | 452114   |
| DAU4019 | HONDA     | WAVE       | 5775     |
| DBB32   | PERODUA   | KANCIL     | 57875    |
| DBB321 | HONDA     | SIVICMAYAT | 5755     |
| DBF15   | PERODUA   | VIVA       | 57775    |
| DBG123 | PERODUA   | PERSONA    | 85585    |
| TF81    | HONDA     | CIVIC      | 213321   |
| WAC5887 | PERODUA   | MYVI       | 123456   |
+---------+-----------+------------+----------+
10 rows in set (0.00 sec)

mysql> insert into Vehicle values
    -> ('WHN1234','PROTON','PERSONA','M111');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Vehicle values
    -> ('WHW4536','HYUNDAI','SONATA','M123');
Query OK, 1 row affected (0.03 sec)

mysql> select*
    -> from Vehicle;
+---------+-----------+------------+----------+
| Car_No | Type      | Model      | Owner_ID |
+---------+-----------+------------+----------+
| AR15    | MITSUBSHI | LANCER     | 150819   |
| BCF1007 | PERODUA   | KENARI     | 234540   |
| BFG1254 | PERODUA   | KELISA     | 452114   |
| DAU4019 | HONDA     | WAVE       | 5775     |
| DBB32   | PERODUA   | KANCIL     | 57875    |
| DBB321 | HONDA     | SIVICMAYAT | 5755     |
| DBF15   | PERODUA   | VIVA       | 57775    |
| DBG123 | PERODUA   | PERSONA    | 85585    |
| TF81    | HONDA     | CIVIC      | 213321   |
| WAC5887 | PERODUA   | MYVI       | 123456   |
| WHN1234 | PROTON    | PERSONA    | M111     |
| WHW4536 | HYUNDAI   | SONATA     | M123     |
+---------+-----------+------------+----------+
12 rows in set (0.00 sec)

mysql> insert into Owner values
    -> ('1245','Adi','1999/01/19','350.00','600.00','CASH','KELANTAN');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('12456','Adawiah','1999/01/30','450.00','650.00','Installment','KELANTAN
');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('24756','Ayub','2000/01/10','400.00','750.00','Installment','KELANTAN');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('2468','Mat','2000/02/21','400.00','450.00','Cash','KELANTAN');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('24689','Shah','2000/02/29','500.00','600.00','CASH','Kuala krai');
Query OK, 1 row affected (0.05 sec)

mysql> select*
    -> from Owner;
+----------+----------+------------+----------+-------------+-------------+-----
-------+
| Owner_ID | Name     | PymtDate   | PymtMade | ActualPrice | PymtType    | Addr
ess    |
+----------+----------+------------+----------+-------------+-------------+-----
-------+
| 123456   | Harrison | 1999-01-23 |   610.00 |      610.00 | Cash        | Bang
i      |
| 1245     | Adi      | 1999-01-19 |   350.00 |      600.00 | CASH        | KELA
NTAN   |
| 12456    | Adawiah | 1999-01-30 |   450.00 |      650.00 | Installment | KELA
NTAN   |
| 150819   | Ackim    | 1999-01-25 |   650.00 |      650.00 | Cash        | Sent
ul     |
| 213321   | Aziz     | 1999-01-31 |   350.00 |      700.00 | Installment | Klan
g      |
| 2468     | Mat      | 2000-02-21 |   400.00 |      450.00 | Cash        | KELA
NTAN   |
| 24689    | Shah     | 2000-02-29 |   500.00 |      600.00 | CASH        | Kual
a krai |
| 24756    | Ayub     | 2000-01-10 |   400.00 |      750.00 | Installment | KELA
NTAN   |
| 452114   | Baker    | 1999-01-31 |   450.00 |      600.00 | Installment | Gomb
ak     |
+----------+----------+------------+----------+-------------+-------------+-----
-------+
9 rows in set (0.00 sec)

mysql> select Name,Car_NO from Vehicle,Owner
    -> where owner.Owner_ID = Vehicle.Owner_ID and type='PERODUA';
+----------+---------+
| Name     | Car_NO |
+----------+---------+
| Baker    | BFG1254 |
| Harrison | WAC5887 |
+----------+---------+
2 rows in set (0.00 sec)

mysql> select count(Car_NO), type from Vehicle where type = 'PERODUA'
    -> Group by type;
+---------------+---------+
| count(Car_NO) | type    |
+---------------+---------+
|             6 | PERODUA |
+---------------+---------+
1 row in set (0.02 sec)

mysql>

Database - mysql queries -part3

2011-07-16T06:40:00.000-07:00

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

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

mysql> use adi
Database changed
mysql> select *
    -> from customer;
+---------+-----------+---------------+----------+----------+---------------+---
-------+--------------+--------+
| Cust_No | Cust_Name | Street        | State    | Zip_Code | City          | Ba
lance | Credit_Limit | Rep_No |
+---------+-----------+---------------+----------+----------+---------------+---
-------+--------------+--------+
| 148     | Mohd Zaid | Jln Merbuk    | Selangor |    58000 | Kota Lama     | 6
550.00 |      7500.00 | 20     |
| 282     | Fikri     | Wangsa Maju   | KL       |    53300 | Wangsa Maju   |
431.00 |     10000.00 | 35     |
| 356     | Azwa      | Setia Wangsa | KL       |    54200 | Setia Wangsa | 5
785.00 |      7500.00 | 65     |
| 408     | Bul       | Taiping       | Perak    |    34600 | Kamunting     | 5
285.00 |      5000.00 | 35     |
| 462     | Afzal     | SD            | Selangor |    52000 | Damansara     | 3
412.00 |     10000.00 | 65     |
| 524     | Shame     | Jln Laksamana | Perak    |    36000 | Teluk Intan   | 12
762.00 |     15000.00 | 20     |
| 608     | Lina      | Taiping       | Perak    |    36000 | Teluk Intan   | 2
106.00 |     10000.00 | 65     |
| 687     | Jack      | Kuching       | Serawak |    26000 | kuching       | 2
851.00 |      5000.00 | 35     |
| 725     | Man       | Machang       | Kelantan |    18300 | Taman Permata |
248.00 |      7500.00 | 35     |
| 842     | Adi       | Kuala Krai    | Kelantan |    18000 | Kg Enggong    | 8
221.00 |      7500.00 | 20     |
+---------+-----------+---------------+----------+----------+---------------+---
-------+--------------+--------+
10 rows in set (0.00 sec)

mysql> select Cust_No, cust_Name, Balance from customer;
+---------+-----------+----------+
| Cust_No | cust_Name | Balance |
+---------+-----------+----------+
| 148     | Mohd Zaid | 6550.00 |
| 282     | Fikri     |   431.00 |
| 356     | Azwa      | 5785.00 |
| 408     | Bul       | 5285.00 |
| 462     | Afzal     | 3412.00 |
| 524     | Shame     | 12762.00 |
| 608     | Lina      | 2106.00 |
| 687     | Jack      | 2851.00 |
| 725     | Man       |   248.00 |
| 842     | Adi       | 8221.00 |
+---------+-----------+----------+
10 rows in set (0.00 sec)

mysql> select Cust_Name from customer where cust_No='687';
+-----------+
| Cust_Name |
+-----------+
| Jack      |
+-----------+
1 row in set (0.00 sec)

mysql> select Cust_Name from customer where rep_No='35';
+-----------+
| Cust_Name |
+-----------+
| Fikri     |
| Bul       |
| Jack      |
| Man       |
+-----------+
4 rows in set (0.00 sec)

mysql> select Cust_Name from customer where balance>=credit_limit;
+-----------+
| Cust_Name |
+-----------+
| Bul       |
| Adi       |
+-----------+
2 rows in set (0.00 sec)

mysql> select Cust_name, City from customer where Balance<='400';
+-----------+---------------+
| Cust_name | City          |
+-----------+---------------+
| Man       | Taman Permata |
+-----------+---------------+
1 row in set (0.00 sec)

mysql> select Cust_name, Cust_No, (credit_Limit-Balance) from customer;
+-----------+---------+------------------------+
| Cust_name | Cust_No | (credit_Limit-Balance) |
+-----------+---------+------------------------+
| Mohd Zaid | 148     |                 950.00 |
| Fikri     | 282     |                9569.00 |
| Azwa      | 356     |                1715.00 |
| Bul       | 408     |                -285.00 |
| Afzal     | 462     |                6588.00 |
| Shame     | 524     |                2238.00 |
| Lina      | 608     |                7894.00 |
| Jack      | 687     |                2149.00 |
| Man       | 725     |                7252.00 |
| Adi       | 842     |                -721.00 |
+-----------+---------+------------------------+
10 rows in set (0.00 sec)

Database - mysql queries -part2

2011-07-16T06:39:00.003-07:00

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.09 sec)

mysql> use adi;
Database changed
mysql> create table Staff
    -> (StaffNo char(3) primary key,
    -> FName char(20),
    -> LName char(20),
    -> Position char(15),
    -> Gender char(10),
    -> DOB Date,
    -> Salary decimal(8,2));
Query OK, 0 rows affected (0.16 sec)

mysql> insert into Staff values
    -> ('102','Mukhlas','Emlia','Manager','Female','1965-06-01','3000.00');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Staff values
    -> ('145','Ann','Bandar','Assistant','Female','1960-11-10','1200.00');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Staff values
    -> ('216','Daudi','Fayadi','Supervisor','Male','1970-02-19','1800.00');
Query OK, 1 row affected (0.02 sec)

mysql> insert into Staff values
    -> ('235','Anis','Harun','Assistant','Female','1968-05-24','1150.00');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Staff values
    -> ('254','Julie','Khan','Manager','Female','1965-11-13','2000.00');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Staff values
    -> ('236','Hashim','Kamal','Supervisor','Male','1966-12-16','1900.00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Staff values
    -> ('311','Foo','Fatt','Supervisor','Male','1978-05-17','1850.00');
Query OK, 1 row affected (0.02 sec)

mysql> insert into Staff values
    -> ('324','Dubbu','Ibrahim','Manager','Male','1968-08-18','2450.00');
Query OK, 1 row affected (0.02 sec)

mysql> select*
    -> from Staff;
+---------+---------+---------+------------+--------+------------+---------+
| StaffNo | FName   | LName   | Position   | Gender | DOB        | Salary |
+---------+---------+---------+------------+--------+------------+---------+
| 102     | Mukhlas | Emlia   | Manager    | Female | 1965-06-01 | 3000.00 |
| 145     | Ann     | Bandar | Assistant | Female | 1960-11-10 | 1200.00 |
| 216     | Daudi   | Fayadi | Supervisor | Male   | 1970-02-19 | 1800.00 |
| 235     | Anis    | Harun   | Assistant | Female | 1968-05-24 | 1150.00 |
| 236     | Hashim | Kamal   | Supervisor | Male   | 1966-12-16 | 1900.00 |
| 254     | Julie   | Khan    | Manager    | Female | 1965-11-13 | 2000.00 |
| 311     | Foo     | Fatt    | Supervisor | Male   | 1978-05-17 | 1850.00 |
| 324     | Dubbu   | Ibrahim | Manager    | Male   | 1968-08-18 | 2450.00 |
+---------+---------+---------+------------+--------+------------+---------+
8 rows in set (0.03 sec)

mysql> select StaffNo,FName,LName,Position,Gender,DOB Date from Staff;
+---------+---------+---------+------------+--------+------------+
| StaffNo | FName   | LName   | Position   | Gender | Date       |
+---------+---------+---------+------------+--------+------------+
| 102     | Mukhlas | Emlia   | Manager    | Female | 1965-06-01 |
| 145     | Ann     | Bandar | Assistant | Female | 1960-11-10 |
| 216     | Daudi   | Fayadi | Supervisor | Male   | 1970-02-19 |
| 235     | Anis    | Harun   | Assistant | Female | 1968-05-24 |
| 236     | Hashim | Kamal   | Supervisor | Male   | 1966-12-16 |
| 254     | Julie   | Khan    | Manager    | Female | 1965-11-13 |
| 311     | Foo     | Fatt    | Supervisor | Male   | 1978-05-17 |
| 324     | Dubbu   | Ibrahim | Manager    | Male   | 1968-08-18 |
+---------+---------+---------+------------+--------+------------+
8 rows in set (0.00 sec)

mysql> select StaffNo,FName,LName,Salary from Staff;
+---------+---------+---------+---------+
| StaffNo | FName   | LName   | Salary |
+---------+---------+---------+---------+
| 102     | Mukhlas | Emlia   | 3000.00 |
| 145     | Ann     | Bandar | 1200.00 |
| 216     | Daudi   | Fayadi | 1800.00 |
| 235     | Anis    | Harun   | 1150.00 |
| 236     | Hashim | Kamal   | 1900.00 |
| 254     | Julie   | Khan    | 2000.00 |
| 311     | Foo     | Fatt    | 1850.00 |
| 324     | Dubbu   | Ibrahim | 2450.00 |
+---------+---------+---------+---------+
8 rows in set (0.00 sec)

mysql> select FName,LName from Staff Where StaffNo=254;
+-------+-------+
| FName | LName |
+-------+-------+
| Julie | Khan |
+-------+-------+
1 row in set (0.03 sec)

mysql> select StaffNo,DOB,LName from Staff where Gender='Female';
+---------+------------+--------+
| StaffNo | DOB        | LName |
+---------+------------+--------+
| 102     | 1965-06-01 | Emlia |
| 145     | 1960-11-10 | Bandar |
| 235     | 1968-05-24 | Harun |
| 254     | 1965-11-13 | Khan   |
+---------+------------+--------+
4 rows in set (0.03 sec)

mysql> select StaffNo,FName,DOB from Staff where Position='Supervisor';
+---------+--------+------------+
| StaffNo | FName | DOB        |
+---------+--------+------------+
| 216     | Daudi | 1970-02-19 |
| 236     | Hashim | 1966-12-16 |
| 311     | Foo    | 1978-05-17 |
+---------+--------+------------+
3 rows in set (0.00 sec)

mysql> select F.StaffNo,F.FName,S.StaffNo,S.FName,F.Gender from Staff F,Staff S
where F.Gender=S.Gender AND F.Staffno<S.Staffno ORDER By Gender;
+---------+---------+---------+--------+--------+
| StaffNo | FName   | StaffNo | FName | Gender |
+---------+---------+---------+--------+--------+
| 145     | Ann     | 235     | Anis   | Female |
| 235     | Anis    | 254     | Julie | Female |
| 145     | Ann     | 254     | Julie | Female |
| 102     | Mukhlas | 145     | Ann    | Female |
| 102     | Mukhlas | 235     | Anis   | Female |
| 102     | Mukhlas | 254     | Julie | Female |
| 311     | Foo     | 324     | Dubbu | Male   |
| 216     | Daudi   | 324     | Dubbu | Male   |
| 236     | Hashim | 311     | Foo    | Male   |
| 216     | Daudi   | 236     | Hashim | Male   |
| 236     | Hashim | 324     | Dubbu | Male   |
| 216     | Daudi   | 311     | Foo    | Male   |
+---------+---------+---------+--------+--------+
12 rows in set (0.00 sec)

mysql> select F.StaffNo,F.LName,S.StaffNo,S.LName,F.Position from Staff F,Staff
S where F.Position=S.Position AND F.Staffno<S.Staffno ORDER By F.Staffno,S.Staff
No;
+---------+--------+---------+---------+------------+
| StaffNo | LName | StaffNo | LName   | Position   |
+---------+--------+---------+---------+------------+
| 102     | Emlia | 254     | Khan    | Manager    |
| 102     | Emlia | 324     | Ibrahim | Manager    |
| 145     | Bandar | 235     | Harun   | Assistant |
| 216     | Fayadi | 236     | Kamal   | Supervisor |
| 216     | Fayadi | 311     | Fatt    | Supervisor |
| 236     | Kamal | 311     | Fatt    | Supervisor |
| 254     | Khan   | 324     | Ibrahim | Manager    |
+---------+--------+---------+---------+------------+
7 rows in set (0.00 sec)

mysql> select count(Staffno),sum(Salary) from Staff;
+----------------+-------------+
| count(Staffno) | sum(Salary) |
+----------------+-------------+
|              8 |    15350.00 |
+----------------+-------------+
1 row in set (0.00 sec)

Database - mysql queries -part1

2011-07-16T06:37:00.001-07:00

Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
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 adi;
Query OK, 1 row affected (0.01 sec)

mysql> use adi;
Database changed
mysql> create table Supplier
    -> (Supp_Num char(3) primary key,
    -> Supp_Name char(15),
    -> Street char(15),
    -> City char(15),
    -> Balance decimal(7),
    -> Credit_Limit char(7));
Query OK, 0 rows affected (0.30 sec)

mysql> insert into Supplier values
    -> ('001','Adi','Kg Enggong','Kuala krai','7500','8500');
Query OK, 1 row affected (0.09 sec)

mysql> insert into Supplier values
    -> ('002','Adawiah','Kg Pasir Era','Kuala Krai','7500','6500');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Supplier values
    -> ('003','Malikah','Bdr Sulaiman','Port Klang','10000','431');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Supplier values
    -> ('004','Nik Hassan','Bdr Sulaiman','Port Klang','7500','5758');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Supplier values
    -> ('005','Radziah','Pnklg Kubur','K.Baharu','5000','5285');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Supplier values
    -> ('006','Iklima','Damansara','Selangor','15000','12762');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Supplier values
    -> ('007','Ahmad','Kg Enggong','Kuala Krai','11000','2190');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Supplier values
    -> ('008','Sharifuddin','Kg Enggong','Kuala Krai','600','3985');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Supplier values
    -> ('009','Naji','Shah Alam','Selangor','9500','548');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Supplier values
    -> ('010','Qusti','Kg Enggong','Kuala Krai','7500','8221');
Query OK, 1 row affected (0.03 sec)

mysql> Select*
    -> from Supplier;
+----------+-------------+--------------+------------+---------+--------------+
| Supp_Num | Supp_Name   | Street       | City       | Balance | Credit_Limit |
+----------+-------------+--------------+------------+---------+--------------+
| 001      | Adi         | Kg Enggong   | Kuala krai |    7500 | 8500         |

| 002      | Adawiah     | Kg Pasir Era | Kuala Krai |    7500 | 6500         |

| 003      | Malikah     | Bdr Sulaiman | Port Klang |   10000 | 431          |

| 004      | Nik Hassan | Bdr Sulaiman | Port Klang |    7500 | 5758         |

| 005      | Radziah     | Pnklg Kubur | K.Baharu   |    5000 | 5285         |

| 006      | Iklima      | Damansara    | Selangor   |   15000 | 12762        |

| 007      | Ahmad       | Kg Enggong   | Kuala Krai |   11000 | 2190         |

| 008      | Sharifuddin | Kg Enggong   | Kuala Krai |     600 | 3985         |

| 009      | Naji        | Shah Alam    | Selangor   |    9500 | 548          |

| 010      | Qusti       | Kg Enggong   | Kuala Krai |    7500 | 8221         |

+----------+-------------+--------------+------------+---------+--------------+
10 rows in set (0.09 sec)

mysql> select Supp_Num,Supp_Name,Balance from Supplier where balance>='2000'OR b
alance<='5000';
+----------+-------------+---------+
| Supp_Num | Supp_Name   | Balance |
+----------+-------------+---------+
| 001      | Adi         |    7500 |
| 002      | Adawiah     |    7500 |
| 003      | Malikah     |   10000 |
| 004      | Nik Hassan |    7500 |
| 005      | Radziah     |    5000 |
| 006      | Iklima      |   15000 |
| 007      | Ahmad       |   11000 |
| 008      | Sharifuddin |     600 |
| 009      | Naji        |    9500 |
| 010      | Qusti       |    7500 |
+----------+-------------+---------+
10 rows in set (0.03 sec)

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

mysql> Alter table Supplier
    -> Add Rep_Num char(5);
Query OK, 10 rows affected (0.30 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> Select Supp_Name from Supplier where(Supp_Num='007');
+-----------+
| Supp_Name |
+-----------+
| Ahmad     |
+-----------+
1 row in set (0.02 sec)

mysql> Select Supp_Num,Supp_Name,Balance from Supplier;
+----------+-------------+---------+
| Supp_Num | Supp_Name   | Balance |
+----------+-------------+---------+
| 001      | Adi         |    7500 |
| 002      | Adawiah     |    7500 |
| 003      | Malikah     |   10000 |
| 004      | Nik Hassan |    7500 |
| 005      | Radziah     |    5000 |
| 006      | Iklima      |   15000 |
| 007      | Ahmad       |   11000 |
| 008      | Sharifuddin |     600 |
| 009      | Naji        |    9500 |
| 010      | Qusti       |    7500 |
+----------+-------------+---------+
10 rows in set (0.00 sec)

mysql> Select Supp_Num,Supp_Name,Balance from Supplier where (Balance='5000'||ba
lance='10000'||balance='15000');
+----------+-----------+---------+
| Supp_Num | Supp_Name | Balance |
+----------+-----------+---------+
| 003      | Malikah   |   10000 |
| 005      | Radziah   |    5000 |
| 006      | Iklima    |   15000 |
+----------+-----------+---------+
3 rows in set (0.00 sec)

mysql> Select Supp_Num,Credit_Limit from Supplier;
+----------+--------------+
| Supp_Num | Credit_Limit |
+----------+--------------+
| 001      | 8500         |
| 002      | 6500         |
| 003      | 431          |
| 004      | 5758         |
| 005      | 5285         |
| 006      | 12762        |
| 007      | 2190         |
| 008      | 3985         |
| 009      | 548          |
| 010      | 8221         |
+----------+--------------+
10 rows in set (0.00 sec)

mysql> select Count(Supp_Num),Sum(Balance) from Supplier;
+-----------------+--------------+
| Count(Supp_Num) | Sum(Balance) |
+-----------------+--------------+
|              10 |        81100 |
+-----------------+--------------+
1 row in set (0.00 sec)

mysql> Select Count(Supp_Num),Sum(Balance) from Supplier
    -> Group by Credit_Limit
    -> Having count(*)>1;
Empty set (0.06 sec)

mysql> Select Credit_Limit, count(*) from Supplier
    -> Group by Credit_Limit;
+--------------+----------+
| Credit_Limit | count(*) |
+--------------+----------+
| 12762        |        1 |
| 2190         |        1 |
| 3985         |        1 |
| 431          |        1 |
| 5285         |        1 |
| 548          |        1 |
| 5758         |        1 |
| 6500         |        1 |
| 8221         |        1 |
| 8500         |        1 |
+--------------+----------+
10 rows in set (0.01 sec)

Blog Know Recent: VirtaPay (Get $ 25 Free!!!)

2011-07-16T02:08:00.000-07:00

Blog Know Recent: VirtaPay (Get $ 25 Free!!!)