Friday, March 29, 2013

[how to] get rank in marks database in mysql

[how to] get rank in marks database in mysql

get rank in marks database in mysql

Posted: 29 Mar 2013 06:55 PM PDT

I have a Student database having one table EXAM. 

id    1  2  3  4  5  marks 7 10  7 10  9

Question is how to give rank like

+------+  | rank |  +------+  |    3 |  |    1 |  |    3 |  |    1 |  |    2 |  +------+

I have used a Query:- 

SELECT id, marks, FIND_IN_SET( marks, (  SELECT GROUP_CONCAT( marks  ORDER BY marks DESC )   FROM exam )  ) AS rank  FROM exam;

but it gives rank 

+------+  | rank |  +------+  |    4 |  |    1 |  |    4 |  |    1 |  |    3 |  +------+

What should I do??

Recreate Indexes on 1 billion record table

Posted: 29 Mar 2013 03:55 PM PDT

I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partiton this table on a new Clustered index with date column. I have just enough space (150gb) in the ldf file to grow incase of index rebuild ( with tempdb OFF). Please help me with an efficent way to rebuild the indexes on new Partition function/scheme. Which of the two approaches would be an efficent and less resource consuming way ( OR recommend any other approach)

1.

  • Drop existing CIX and drop all N-CIX's
  • Create CIX on PScheme
  • Create aligned/non-aligned indexes on PScheme

2.

  • Drop only CIX
  • Create CIX on PScheme
  • Create aligned/non-aligned indexes (with DROP_EXISTING) ON

Thanks

MySQL not allowing text columns?

Posted: 29 Mar 2013 01:30 PM PDT

I'm using MySQL 5.5.27. I currently have a BLOB column, that I want to convert to text to allow fulltext searching. The ALTER TABLE command to create a text column is successful, but the ALTER TABLE table ADD FULLTEXT INDEX index (column ASC) to create a fulltext index returns an error, saying I cannot create a fulltext index on that column.

When I go back and look at the specs for the table, it appears as BLOB, instead of text.

I'm using MySQLWorkbench, but I've generally been running the scripts by hand, instead of using the wizard.

I have tried setting a character encoding for the entire schema (none existed before) thinking that this would be needed for text columns, but it didn't help.

How to create view in SQLite using INSERT INTO?

Posted: 29 Mar 2013 12:33 PM PDT

I'm trying to create view that will summarize data in two tables. Each table have several columns, but they both have NAME and AREA columns. And I want these two columns to be united correspondingly in view. I tried to create view using following query:

CREATE VIEW summary AS  INSERT INTO (SELECT Name, SUM(Area) FROM table1 GROUP BY Name)  SELECT *  FROM (SELECT Name, SUM(Area) FROM table2 GROUP BY Name)

But I get the error: SQL error: near "INSERT": syntax error. Actually I tried different querys involving INSERT INTO and it seems that CREATE VIEW will not work with INSERT INTO, and INSERT INTO does not accept subquery - only existing table (even temporal) would do.

How to rewrite CREATE VIEW statement to achieve my goal?

Role of Database Administrators

Posted: 29 Mar 2013 10:46 AM PDT

I am currently working as an application developer on a project which involves the creation of a new SQL Server database. I am actively working with a group of DBAs who will play some role in the creation and maintenence of this database.

I want to understand what role the DBA normally plays in this type of scenario. It seems the DBAs consider everything to be the responsibility of the application team. I designed the schema and indexes, generated the DDL, and am currently in the process of performance testing the database. Are these tasks normally performed by development?

Also, it has been suggested that development should be responsible for the creation and maintenence of the database user accounts. We would be responsible for periodically changing the passwords on the production databases. Is this the norm? It seems to me that it should be done by the DBAs.

How to migrate SQL Server to MySQL

Posted: 29 Mar 2013 03:42 PM PDT

I'm trying to migrate a SQL Server db to MySQL, and I'm having a tough time. I've tried:

  • MySQLWorkbench -- migration tool fails because my login only shows views in SQL Server, not the tables themselves, and so I can't get past the "Schemata Selection" screen.

  • sqlcmd and bcp -- both fail because they can't export csv properly. They don't quote strings with commas in them. Plus, they don't create table schemas in MySQL.

  • SQLyog -- just fails. Creates a MySQL table from a MS SQL view ok, but doesn't import the data. Also takes a really long time on small tables, and I have to import millions of rows.

Anyone have suggestions?

SSIS Visual Studio 2008 reference variable scope in expression builder

Posted: 29 Mar 2013 09:10 AM PDT

When creating an SSIS package in Visual Studio 2008, in the expression builder, I know I can reference a variable by using @[User::FilePath] for example but this only works if you are evaluating the expression for an object that is within the same scope of the variable that you are referencing.

Is it possible to reference the SCOPE of the variable such as @[User:PackageScope:FilePath] or @[User:SQLTaskScope:FilePath] for example so that I can call a variable that is in a different scope then the object I am building the expression for?

How can I check for SQL Server performance problems using T-SQL? [closed]

Posted: 29 Mar 2013 08:59 AM PDT

I know how to use the Profiler, Performance Monitor, and Activity Monitor. I want to find a way to troubleshoot performance using T-SQL. How can I check for Microsoft SQL Server performance problems using T-SQL?

How to create column

Posted: 29 Mar 2013 09:16 AM PDT

I am new to ssrs report designing can any one help me in this

I have two question

1) How to create a column to input user data and after that I need to perform some operations on data enter by the user which i need to display in another column

2)how to make round a decimal to nearest integer ex 6.03 to 6 and 6.52 to 7

SQL Server 2005 Unexpected Disk Usage during Backups

Posted: 29 Mar 2013 10:30 AM PDT

On our SQL Server 2005 SP4 server we have a maintenance plan in place which performs a full backup of selected databases on a nightly basis. The databases are configured in Simple recovery mode and the maintenance plan cleans up backups that are older than 3 days.

The maintenance plan specifies that the backups be stored on the J:\ drive of our server and everything appears to work properly.

Our issue is that around the time the backup step of the maintenance plan finishes we have noticed that the K:\ drive on our server loses free disk space. This will continue until the drive is almost completely full and then the next backup will free up 50-100gb and it will repeat the cycle of taking up disk space and then freeing up the space.

We have used WinDirStat many times to try and find the file that are taking up the space on our K: drive and are unable to locate them. The disk usage appears to be directly associated with the SQL Server maintenance plan that runs.

Is there a way to verify that the backups are not accessing the K:\drive? I can't find any references to the K:\ drive in the SQL Server logs.

Thank you in advance for any assistance you can provide.

Josh

How to undo DROP TABLE statement?

Posted: 29 Mar 2013 01:06 PM PDT

I've accidentally executed DROP TABLE statement on a wrong table. I have backups that are several days old that I can use to recover table and most of the data but not all.

I know that SQL Server can't rollback already committed transaction but I was wondering if there are any alternative ways to get the rest of the data back. Any help such as some uncommon sql scrpts or anything like that would be greatly appreciated.

How to use array variable in query in PostgreSQL

Posted: 29 Mar 2013 10:02 AM PDT

Create table t1 ( xcheck varchar[], name text );    CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])    RETURNS record AS    DECLARE xrc as record;      execute 'select name from t1 where xcheck @> ''' || xch :: varchar[] || ''';' into xrc;   return xrc;  END;

In table t1 having array value and text. I am calling this(fn_acgroup) function with array as its parameter value. In that function, I am checking the array value with passed array if it matches then I will return the name. In that case I get error as operator does not exist: text || character varying[]. Am I doing anything wrong? How can I solve this problem?

event scheduler not called

Posted: 29 Mar 2013 07:22 PM PDT

I had created one event scheduler which looks like this

mysql> show create event event1      -> ;  +--------+----------+-----------+--------------------------------------------------------------------------------------------+  | Event  | sql_mode | time_zone | Create Event  | character_set_client | collation_connection | Database Collation |  +--------+----------+-----------+-----------------------------------------------------------------------+----------------------+----------------------+    | event1 |          | SYSTEM    | CREATE DEFINER=`root`@`localhost` EVENT `event1` ON SCHEDULE EVERY 1 MONTH STARTS '2013-02-02 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN   update samp set col1 =col1  + 1; END | utf8                 | utf8_general_ci      | latin1_swedish_ci  |  +--------+----------+-----------+-----------------------------------------------------------------------+----------------------+----------------------+---------  -----------+  1 row in set (0.00 sec)

This events has not called on 1st of month. So i tried show processlist\g to find it is runnung or not; it showed following o/p

mysql> show processlist\g;  +-----+-----------------+-----------------+---------------+---------+------+---     | Id  | User            | Host            | db            | Command | Time | State                       | Info             |  +-----+-----------------+-----------------+---------------+---------+------+---  | 136 | event_scheduler | localhost       | NULL          | Daemon  | 1855 | Waiting for next activation | NULL    |

so NULL in db col means that no DB is assigned to it?

Please help me to solve it.

Automatic database file creation based on database file size in SQL Server 2008 R2

Posted: 29 Mar 2013 07:53 AM PDT

Now i am using SQL Server 2008 R2 database for data logging. The name of the database profile is MASTERDB. That profile has one data file called MASTERDB.mdf and one log file called MASTERDB_log.ldf. In this database data are logging in every seconds. Here i wants to maintain
the data file size as 4GB. If the size excited the limit then i wants to create one new data file under the same profile. Is there any possible options available in SQL Server 2008 R2?.

Specify Server for DBMS_Scheduler Job in Policy Managed RAC

Posted: 29 Mar 2013 07:18 PM PDT

A unit test requires a dbms_scheduler job to run on the same RAC node as the unit test is being run from. I know that with an Admin managed database this could be done by creating a service that limited the available instances and then using that service in a job class the job uses. My question is, how can this be done in 11.2 with policy management?

Pools can be created that have only a single server in them and databases can be assigned to multiple pools, but as I understand it, a server can only be assigned to a single pool. Therefore, a service can't be created that uses a single server and still have other services that use a pool defined with multiple servers including that one.

I also know that services can be created as either SINGLETON or UNIFORM, but since SIGNLETON doesn't provide for allowed servers or even preferred servers, I'm not sure how this would help.

Surely I am missing something that makes this all possible.

multi-master to single-slave replication at table level with PostgreSQL or MySQL

Posted: 29 Mar 2013 09:57 AM PDT

Here is my scenario

Master1 hosting DB1  Master2 hosting DB2  ...  MasterN hosting DBN    replicate to:    Slave1 hosting DB1,DB2... DBN

I've read similar questions and they recommend to start different instances at Slave1 and simply do MasterN-Slave1(instanceN) replication, as instructed here:

Single slave - multiple master MySQL replication

That would be piece of cake, but running different instances might be a waste of resources.

I really want to achieve this with an single DBMS instance at Slave1, and if possible with PostgreSQL; but can try with MySQL if there is a solution for this.

Any help is appreciated.

Does fast bcp replicate in sybase sql statement replication?

Posted: 29 Mar 2013 11:57 AM PDT

Is it possible to replicate fast bcp using sql statement replication in sybase?

What are the performance implications of creating an index in MySQL?

Posted: 29 Mar 2013 12:57 PM PDT

The MySQL reference guide doesn't (from what I see) detail the implications of creating an index; by this I mean the performance implications and any locks it may take on the table or columns against which is building the index.

If anyone could give me an idea as to what the implications are, it would be appreciated; what would be appreciated even more is a link to some documentation where I can answer this question for myself!

Regards,

Chris :D

SQL server enterprise vs standard

Posted: 29 Mar 2013 07:57 AM PDT

I am studying the difference between SQL server enterprise and standard. The one feature that I find beneficial from this page is:

http://www.microsoft.com/sqlserver/en/us/editions.aspx

Advanced High Availability (Multiple, Active Secondaries; Multi-site, Geo-Clustering)

How exactly does it ensure advanced high availability? Does anybody have any details?

Trouble setting up Nagios to monitor Oracle services

Posted: 29 Mar 2013 10:57 AM PDT

I've got an install of Nagios XI that doesn't seem to want to talk to any of my Oracle services here. I've pulled out the monitoring command and am running it manually, after setting ORACLE_HOME and LD_LIBDRARY_PATH of course, but it keeps generating the following error:

/usr/local/nagios # libexec/check_oracle_health --connect "oracle-server:1551" --username user --password "pass" --name OFFDB1 --mode tablespace-can-allocate-next --warning 20 --critical 30  CRITICAL - cannot connect to oracle-server:1551. ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA (DBD ERROR: OCIServerAttach)

I'm still fairly new to Oracle, but my googlings seem to indicate that SERVICE_NAME should match the GLOBAL_DBNAME in listener.ora, which is OFFDB1. Do I need to do something else here like modify the connect string?

As a note, there are multiple instances of oracle sharing the target box, but each seems to be intalled to separate partitions and are running their own listeners or various ports.

innodb changing row format doesn't decrease table size?

Posted: 29 Mar 2013 07:57 PM PDT

We're currently using MySQL with innodb and we have some large tables that are compact in row format. When I change the row format to compressed we are still seeing the same size for the table. Anyone know the reason for this?

Can I use a foreign key index as a shortcut to getting a row count in an INNODB table?

Posted: 29 Mar 2013 02:57 PM PDT

I have a table that has a large number of rows in it.

The primary key (an auto-incrementing integer) is, by default, indexed.

While waiting for a row count to be returned I did an EXPLAIN in another window and the the results were as follows:

mysql> SELECT COUNT(1) FROM `gauge_data`;  +----------+  | COUNT(1) |  +----------+  | 25453476 |  +----------+  1 row in set (2 min 36.20 sec)      mysql> EXPLAIN SELECT COUNT(1) FROM `gauge_data`;  +----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+  | id | select_type | table      | type  | possible_keys | key             | key_len | ref  | rows     | Extra       |  +----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+  |  1 | SIMPLE      | gauge_data | index | NULL          | gauge_data_FI_1 | 5       | NULL | 24596487 | Using index |  +----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+  1 row in set (0.13 sec)

Since the primary key is guaranteed to be unique, can I just take the number of rows from the EXPLAIN and use that as the row count of the table?

BTW, I believe the difference in numbers is due to the fact that more data is continually being added to this table.

Database setup/design for multiple services

Posted: 29 Mar 2013 01:57 PM PDT

I am working on a new MySQL database that will store orders/customer information for around 15 different services. 7 of the services are similar in regards to the information that is stored with a few of the 7 services requiring an additional piece of information. There other 7-8 services are similar to each other as well but not as similar the the first 7. So my question is how should I break this down into a MySQL database?

Should each service have its own table? If this is true, what is the best way to link tables? Any advice is greatly appreciated!

Also, here is a sample of the type of info that will be stored:

enter image description here

Automate daily backup of database

Posted: 29 Mar 2013 10:46 AM PDT

I am trying to create automate/daily backup of database in SQL Server 2008 R2. I did bit of research and I get to know that as I am using Express edition; which doesn't come with Maintenance Plan, I can't create database backup automate/daily basis. So, the only possibilities are I have to use either TSQL or create Job. I don't have much understanding of SQL Job, so I am left with T-SQL only.

Can anyone explain me how to do automate backup of database in T-SQL using stored procedure or is there any other options I have to do that.

Regards.

What is connection time?

Posted: 29 Mar 2013 08:10 PM PDT

We are currently trialling ManageEngine SQLDBManager Plus. On the dashboard for a server it shows connection time in milliseconds (currently 15ms).

Can anyone tell me what Connection Time represents and/or what I could use this metric for?

We are monitoring a MS SQL Server 2005 database if that makes any difference.

Is there a quickish, straighforward way to measure Transactions Per Second on SQL Server 2000?

Posted: 29 Mar 2013 08:08 PM PDT

The sys.dm_os_performance_counters DMV introduced in SQL Server 2005 makes this task somewhat trivial. However, my Google-Fu has been blocked in finding a way to do this in SQL Server 2000.

How can I measure transactions per second in SQL Server 2000?

oracle format specifiers: to_number vs to_char

Posted: 29 Mar 2013 07:21 PM PDT

SQL> select TO_NUMBER(123.56,'999.9') from dual;  select TO_NUMBER(123.56,'999.9') from dual                   *  ERROR at line 1:  ORA-01722: invalid number      SQL> select TO_CHAR(123.56,'999.9') from dual;    TO_CHA  ------   123.6    SQL>

I am having a hard time in understanding the nuances of Oracle SQL. For example, have a look at the two queries above.

Why does first query fail but second one succeed?

How do I find the median value of a column in MySQL?

Posted: 29 Mar 2013 05:49 PM PDT

I can only imagine doing this with two database queries. The first finds the number of rows in the database, and the second selects with an ORDER BY on the column I am interested in and LIMIT X, 1 where X is half the number of rows.

Is there a simple way to do this with only one query?

Right now I am using averages in my calculations, but I think the mean would be better; there is no upper bound to the values and they are bounded from below by 0.

EDIT: yes, I meant to say 'median' but was having some brain error & searched for 'mean'. I have now found the answer over at stackoverflow
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog